ACCESS SQL STRING problem

[armesca]

I can't get this to run in Access vba. I get a too few parameters error. Expected 1. The problem lies here I believe : [All details table] WHERE ([updated max transaction] + 30)>=#" & Me.txt_cfodate2 & "#
I think it is a date error, the txtbox I pull from is a date as well and updated max transaction is a date field in a table. Any help is greatly appreciated!! Thanks.

sqlstring = "Select TOP 10 [Obligation Number],[Vendor name],[Vendor state code],[Current obligated amount],[draw downs],[Award balance],[updated max transaction] FROM [All details table] WHERE ([updated max transaction] + 30)>=#" & Me.txt_cfodate2 & "# AND [updated max transactoin]<> #12/31/1999# AND [Award Balance]>5 AND [Award Status] = 'OPEN' AND [Fiscal Year/Program] Like '*2006" & " " & mycrit & " ORDER BY [updated max transaction]ASC;"
MsgBox sqlstring
Set rs = Application.CurrentDb.OpenRecordset(sqlstring)
objws.Range("A18").CopyFromRecordset rs
rs.Close

 

[pbaldy]

Change

MsgBox sqlstring

to

Debug.Print sqlstring

which will print out the finished SQL to the VBA Immediate window. If you don't spot the problem, post the SQL here. Offhand, there's no space in front of "ASC".

 

[vbaInet]

Me.txt_cfodate2 is returning an invalid date.

 

[Galaxiom]

The date from Me.txt_cfodate2 needs to be converted to mm/dd/yyyy.

I do this and add the hashes in one step
Format(Me.txt_cfodate2, "\#mm\/dd\/yyyy\#")