MS Access - Date Condition Not working

mattyhousecat

New Member
Joined
Nov 9, 2017
Messages
11
Hey Guys,

I have some SQL code that updates a Schedule table, which is working;

SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
"tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
"tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
"tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
"WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
"And (tblSchedule.SchDate) => " & DateFrom & ");"

however on the bottom row "And (tblSchedule.SchDate) => " & DateFrom & ");"
the condition doesnt seem to be working correctly; if I have dates in Oct,Nov and December

and the DateForm value is mid November; the update will change all of the records; regardless of the date condition but I have set it to greater than equal to

can anyone see what im doing wrong?

thanks
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You should view your SQL to confirm the actual SQL command text (which is also much easier to debug since thats the actual SQL command you are sending to the database for execution):


Code:
SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
"tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
"tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
"tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
"WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
"And (tblSchedule.SchDate) => " & DateFrom & ");"
[B][COLOR="#FF0000"]DEBUG.PRINT SQL_tblSchedule_Part2[/COLOR][/B]

or (message boxes are not always as easy for viewing long text values but it seems some people don't know how to view debug.print messages in the immediate window):
Code:
SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
"tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
"tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
"tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
"WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
"And (tblSchedule.SchDate) => " & DateFrom & ");"
[B][COLOR="#FF0000"]MsgBox SQL_tblSchedule_Part2[/COLOR][/B]

So, what is your actual sql command here? Most likely you are not correctly formatting the date for use in SQL.

Note that this is not a valid sql operator as far as I know:
Code:
=>

I assume you mean:
Code:
>=

I guess it's possible that is the problem too and it has nothing to do with the date.
 
Last edited:
Upvote 0
Respectfully, "doesn't work" (same as "isn't working") makes everyone guess. Error numbers, messages can help a lot. If none, then elaborate on what's not happening that should be. I suspect you got a syntax error like "Missing Operator..."
It's always greater than or less than first, equal sign second. Wrong (=> or =<). Right (>= or <=)
Agree that the lack of date delimiters (#) is/will be a problem as well
"And (tblSchedule.SchDate) >= #" & DateFrom & "#);"
 
Upvote 0
Hey dude, this was the answer why;

SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
"tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
"tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
"tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
"WHERE ((tblSchedule.EstNo) = " & tempEstNo & " )" & _
"And (tblSchedule.SchDate >= #" & DateFrom & "#);"


it was the hashes around the variable; thanks for trying /flicks wrist
 
Upvote 0
it was the hashes around the variable
I kinda doubt that was all. Put the >= back to what you had (=>) and I'll wager it doesn't work again.
What the heck does this mean
/flicks wrist
Sounds like you're giving me the finger or something:(
 
Upvote 0
You do not show where you are located, but as well as the hashes the date has to be mm/dd/yyyy format? for Access SQL

No need for the flick wrist :)

Hey dude, this was the answer why;

SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
"tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
"tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
"tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
"WHERE ((tblSchedule.EstNo) = " & tempEstNo & " )" & _
"And (tblSchedule.SchDate >= #" & DateFrom & "#);"


it was the hashes around the variable; thanks for trying /flicks wrist
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top