problem with date formatting

[mary555]

I'm trying to compare a date in a table (which is in medium format) and the current date. I am using a recordset and this is the code:

Set rs = db.OpenRecordset("SELECT * FROM Alarms Where Alarms.Alarm<" & "#" & TimeValue(Time) & "#" & " And (Alarms.[Alarm Date] > " & Format(Date, "medium date") & " Or Alarms.[Alarm Date] = " & Format(Date, "medium date") & ")")

However, i'm getting an error saying that there are too few parameters (Error 3061)
Can anyone tell me how to fix this...I can't see anything wrong!

 

[lupins46]

'Too few parameters' usually points to a spelling error.

I suggest you do a debug.print of your sql string so that you can see what you are really doing.

Dim strSQL
strsql = "SELECT * FROM Alarms Where Alarms.Alarm<" & "#" & TimeValue(Time) & "#" & " And (Alarms.[Alarm Date] > " & Format(Date, "medium date") & " Or Alarms.[Alarm Date] = " & Format(Date, "medium date") & ")"
Debug.print strsql

Set rs = .....

 

[mary555]

i'm not familiar with debugging...i put that code u wrote into my program, but it didn't do anything...what is it supposed to do?

 

[PHV]

First, why having 2 columns to store a single DateTime value ?
You have to use # for the date too:
... And (Alarms.[Alarm Date] > #" & Format(Date, "yyyy-mm-dd") & "# Or Alarms.[Alarm Date] = #" & Format(Date, "yyyy-mm-dd") & "#)")

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

 

[mary555]

also i checked and theres no spelling mistake...what else can it be?

 

[dhookom]

You would need to delimit your date like you do time.
However, I would try:

Set rs = db.OpenRecordset("SELECT * FROM Alarms " & _
   "Where Alarm<Time() And Alarms.[Alarm Date] >= Date()")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mary555]

im just not really familiar with how to manipulate dates/time. thats why i used 2...do you see an easier way to do this? I have a user enter a time for somethingand if the time is after midnight then I want it to set the date to the next day obviously. is there a way to do this?

 

[mary555]

putting the # with date got rid of my error...I didn't realize I needed that for dates too because in another place in my program, I didn't put the # signs and it worked.

 

[mary555]

I have a table:
ID A Date Current Time ATime Comments
72 16-Nov-05 10:29 11:29 test

Now I need a msgbox to appear when the current time is < the A date and time...what would be the statement to do this?

 

[dhookom]

Queries and tables don't invoke message boxes. I'm not sure of your objective with this question.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mary555]

from a form

 

[dhookom]

If you want a message box to pop up when a user enters a particular value or range in a text box, you can use the after update event.

If me.txtATime > Time Then
  MsgBox "You entered a future time", vbOkOnly+vbInformation, "PEBKAC"
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]