Need syntax help with my SQL statement 1

[ruthcali]

i am using Access97.

i have an SQL statement with two conditions for the WHERE clause and i am having trouble with the syntax.

this is what i have tried:
"where [RevisionDate] between " & Forms![frmForm1]![txtStartDate] & " And " & Format([Forms]![frmForm1]![txtEndDate], "short date&quot & " and " & "city= " & Forms![frmForm1]![cboCity].Column(1)

but with my MsgBox rec.RecordCount, i get 'no records found' even though i know there are records.

RevisionDate is a date field

TxtStartDate is an unbound text box with a format of Short Date.

txtEndDate is an unbound text box with a format of General Date and an input mask of 99/99/00" 11":"58":"58 PM";0;_ The input mask means it adds 11:58:58 to the date. (that way i am ensured of having all records on the date requested. for example, before, if i had a record with a date of 06/30/01 2:45:00pm and a user wanted to see all records between 01/01/01 and 06/30/01, that record wouldn't be included. but now that i changed it to show 06/30/01 11:58:58, that record would be included.)

The city part of the Where clause works by itself. so if my WHERE clause is just "where city= " & Forms![frmRptKPI]![cboCity].Column(1) it works pefectly.

so i need help with the Between And for a date field.

Thanks,
ruth
ruth.jonkman@wcom.com

 

[lachie]

With your sql statement (in jet 3.51 / Access 97 at least) you will need to qualify your dates with a '#'. ie. #01/01/2001#

So this would change your SQL Statement to

"WHERE [RevisionDate] BETWEEN #" & Forms![frmForm1]![txtStartDate] & "# AND #" & Format([Forms]![frmForm1]![txtEndDate], "short date&quot & "# AND city= " & Forms![frmForm1]![cboCity].Column(1)

That said, you should NOT get these dates from controls bound on a form, this has the negative effect of tying your underlying queries to how your user interface looks - a big no no!

You should also format the txtStartDate using the Format command as well, it will help you isolate problems.

 

[ruthcali]

You are a genius!! it worked. i have spent hours on this. thank you so so so much!!!!!


i knew it could be done. i was so frustrated. i couldn't find any info on how to write a Between statement in SQL. you are my savior. thank you again.


i'm not sure i understand what you are saying is a No-no.

i have a form where there are two unbound text boxes and one unbound list box. the user enters a beg and an ending date from the text boxes and picks a city from the list box. Then they click on a command button to see a report for that data. Are you saying that is not good programming practice?

And regarding your last comment, i have TxtStartDate is an unbound text box with a format of Short Date. Is that not correct? how else should i format it?

THANK YOU


ruth.jonkman@wcom.com