Date Comparison

[pauljkeenan]

Hi everyone,

I have been searching various sites to try and solve this problem but as of yet I cant find a solution. when I change the syntax I get a different error each time

Im trying to compare a string entered to a text box(via a popup calendar) and those in a table. But I get a syntax error - missing operator message everytime.

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " (qrySearchVisaSub.App Date) Like #" & Format(Me.txtStartDate, "dd-mmm-yyyy") & "# AND"
End If

any ideas? ive been mucking around with it but cant seem to solve it, oh no

any help will be very much appreciated

 

[jebry]

Hi!

I'm not sure where the problem might be but you do need [] around App Date:

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " (qrySearchVisaSub.[App Date]) Like #" & Format(Me.txtStartDate, "dd-mmm-yyyy") & "# AND"
End If

Also, since txtStartDate is already a string you probably don't need to use the Format statement. If the format is critical then you may need to change it to a date first:

Format(CDate(Me!txtStartDate), "dd-mmm-yyyy")

Finally, I'm not sure what you are trying to accomplish with the Like, but wildcards are usually used with the Like keyword. and it is not used with a date data type.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[pauljkeenan]

Thanks Jeff for the replying again

still the same problem there unfortunately, thought it might be the names I used for the colums in the table but Ive changed that to app_date and it doesnt work with or without the brackets. also I removed the format func. but still get the same error

syntax error in date in query expression '(qrySearchVisaSub.App_Date) like #25.8.06#'


so instead of using like what should I use for normal string comparison without wildcards? I've only started access last week, and use 'like' for most of my comparisons.

cheers

 

[PHV]

Provided App_Date is defined as Date:
strWhere = strWhere & " qrySearchVisaSub.App_Date=#" & Format(Me!txtStartDate, "yyyy-mm-dd") & "# AND"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeThru]

As PHV said, the 'like' wont do anything if it is a date (not a string)

Also, be aware of UK and other date formats - I have to reformat all dates from UK to US format before using them for SQL queries.

 

[PHV]

I have to reformat all dates from UK to US format before using them for SQL queries
Unnecessary stuff if you use a non ambiguous date format as the ANSI like I posted.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauljkeenan]

hey guys,

sorry for late reply, thanks so much for your help - it works perfectly now. Id be lost without this forum, really.

strWhere = strWhere & " (qrySearchVisaSub.App_Date) = #" & Format(Me!txtStartDate, "dd-mm-yyyy") & "# AND"

this is the format that works for me, thanks again lads,

go raibh mille maith agat