Formatting question

[RichardH]

Hi, I'm going mad trying to add this line to an sql statement. It works fine without it but I get an error saying one or more parameters is missing.

Me.StartDate = Me.CalStart.Value
Me.EndDate = Me.calEnd.Value

strSQL = strSQL + " AND ((tblResults.DateNumeric) Between ('" & Me.StartDate & "') And ('" & Me.EndDate & "')) "

The string when it's put together looks like this...

((tblResults.DateNumeric) Between ('22/06/2005') And ('22/06/2005'))

Please can someone help, I'm fairly new to this.

thanks

 

[KeithIT]

If you want to declare the date value then you need to use # signs not '. Concatenate two strings with an ampersand, not a +, and put a semi-colon at the end of your SQL statement.

strSQL = strSQL & " AND ((tblResults.DateNumeric) Between (#" & Me.StartDate & "#) And (#" & Me.EndDate & "#));"

Try that.

 

[Mile-O]

If you want to declare the date value then you need to use # signs not '. Concatenate two strings with an ampersand, not a +, and put a semi-colon at the end of your SQL statement.

strSQL = strSQL & " AND ((tblResults.DateNumeric) Between (#" & Me.StartDate & "#) And (#" & Me.EndDate & "#));"

One thing I would add is to ensure that you are querying based on the US date format.

i.e.

strSQL = strSQL & " AND ((tblResults.DateNumeric) Between (#" & Format(Me.StartDate, "mm/dd/yyyy") & "#) And (#" & Format(Me.EndDate, "mm/dd/yyyy") & "#));"

 

[RichardH]

Thanks for the quick reply. I tried it and I still get the same response.

This is the code....

Me.StartDate = Me.CalStart.Value
Me.EndDate = Me.calEnd.Value

strSQL = strSQL & " AND ((tblResults.DateNumeric) Between (#" & Me.StartDate & "#) And (#" & Me.EndDate & "#)) "

strSQL = strSQL & "ORDER BY tblInitDates.DateNumeric ASC;"

and this is the string with values completed...

AND ((tblResults.DateNumeric) Between (#01/01/2005#) And (#22/06/2005#)) ORDER BY tblInitDates.DateNumeric ASC;

I'm stuck! I don't understand which parameter doesn't have a value, when they all obviously do.
Thanks for any help you can give me, I appreciate it.

 

[Mile-O]

Post the complete code then please.

 

[RichardH]

DOH!! spot the stupid mistake. Sorry to waste your time.

 

[Mile-O]

What was it?

 

[KeithIT]

Could you post your solution so that anyone else searching for this problem can find it. Thanks!

 

[RichardH]

I was filtering using a field in a table I was creating. If I'd copied the whole code ( which would have taken up too much room) it would have shown that I'm doing a SELECT INTO statement.
The table I was creating was tblResults and I was trying to filter on data that wasn't there yet...

strSQL = strSQL & " AND ((tblResults.DateNumeric) Between (#" & Me.StartDate & "#) And (#" & Me.EndDate & "#)) "

strSQL = strSQL & "ORDER BY tblInitDates.DateNumeric ASC;"

and this is the string with values completed...

AND ((tblResults.DateNumeric) Between (#01/01/2005#) And (#22/06/2005#)) ORDER BY tblInitDates.DateNumeric ASC;

If I change it to filter by the data in tblInitDates.DateNumeric it works fine. I've learnt a few lessons doing this, 1. post complete info to the forum, 2. take breaks so I don't keep looking at the same code and expecting different things to happen.

Thanks for taking the time to look anyway