MS Access - Date Condition Not working

[mattyhousecat]

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

 

[mattyhousecat]

DateFrom = Format(Forms!frmChangeEstName!EstDateFrom, "Short Date")

which doesnt matter but yeah

 

[xenou]

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):

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):

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:

=>

I assume you mean:

>=

I guess it's possible that is the problem too and it has nothing to do with the date.

 

[Micron]

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 & "#);"

 

[mattyhousecat]

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

 

[Micron]

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

 

[mattyhousecat]

i love you Micron <3
/kisses

 

[welshgasman]

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

 

[mattyhousecat]

Sorry new member, im from the land down under; Australia