SQL in VBA

[ECEstudent]

Hi, My code keeps bring up an error every time I compile this:

"Run-time error '3061': Too few parameters. Expected 1."


Can someone please help? I'm not quite sure what the problem is but I think it might have to do with the fact that I'm using 2 AND in the statement below. How can I format it so it would work? Thanks in advance!

Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId=" & varCod & " AND OrderDate BETWEEN #09/30/2001# AND #10/01/2012#")

 

[Isskint]

The only thing i can see there, is varCod variable type the same as SerialCardId field type (EG both numbers?) If they are strings then you need to include apostrophes and if they are not the same type - they need to be!

 

[ECEstudent]

Isskint? Alexandrian? I like the pic and thanks! I got it figured out. It's compiling but not actually bringing up the correct values that I need. I think it's something with how the dates are formatted in the table. hmn.

 

[ECEstudent]

This is what I did:
Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")

It's no longer giving me an error but it's also not really working. It's returning values in the SerialNumberCustomer table but not the values between 9/30/2001 and 10/01/2012

I tried formating the dates in my table as mm/dd/yyyy when before it was m/d/yyyy for some while others it was mm/dd/yyyy but that didn't help much. I'm just messing around with it some more until I can see what the problem is exactly. Thanks for the help!

 

[Uncle Gizmo]

If your dates are stored as Date/Time then this can cause problems.

 

[Isskint]

DateValue() is a (usually) useful function. It will convert any thing that looks like a date into a system date format date. eg DateValue(OrderDate). Dates are the bane of a developers life in access. It does not matter what your date settings are, Access will always see them in american format. So 5th June 2013 is 05/06/13 in UK but in USA it is 06/05/13. The way around this (?) is to always leave date formats in tables and queries as GeneralDate BUT change the display formats to your own needs.

If your dates are stored as Date/Time then this can cause problems.

Too true. You need to consider the time of day also.

 

[ECEstudent]

How do I use DateValue() exactly?

I put in DateValue(ShipDate) but it brought up and error and told me to define ShipDate. ShipDate is the column in the table that has all the dates I want the criteria [ Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ") ] to work for.