splitting dates by day month and year for search query

[OC2]

Hi

thought id attempt to do this, i have a field with the date format for exmple is 4/14/01

i want to make a search query using a form to find a particular date, but instead of typing in 4/14/01, i want to have 3 pull "month" "day" "year" and by selecting the combination, i get the date result in one field.

This is what ive tried so far, im notsure if im on the right track or totally off. This is just a test without using the pull downs but still using 3 text boxes.

Private Sub Search_Click()
Dim db As Database, rst As Recordset
Dim crdate, strsql

crdate = " = '" & Me!Month & Me!Day & Me!year & " '"

strsql = " Select * from [table1] where" _
& " [date] " & crdate & ""

Set db = CurrentDb()
Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
If st.RecordCount < 1 Then
MsgBox &quot;none&quot;, vbOKOnly, &quot;try&quot;
Exit Sub
Else: Forms![form1].RecordSource = strsql
End If

End Sub

table1 is my table, date is the field, and form1 is the form,

thx

 

[MichaelRed]

And the ($64.00) question is?

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[OC2]

oh sorry, didnt realise i forgot to ask the question.

It doesnt work, anyone know how to make it work?

 

[MichaelRed]

Private Sub Search_Click()

Dim db As Database,
Dim rst As Recordset

Dim crdate As Date
Dim strsql As String

[tab]crdate = Me!Month & &quot;/&quot; & Me!Day & &quot;/&quot; & Me!year
[tab]strsql = &quot;Select * from [table1] where&quot; _
[tab] &quot; [date]= &quot; & crdate

[tab]Set db = CurrentDb()
[tab]Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
[tab]If (rst.RecordCount < 1) Then
[tab][tab]MsgBox &quot;none&quot;, vbOKOnly, &quot;try&quot;
[tab][tab]Exit Sub
[tab] Else
[tab][tab]Forms![form1].RecordSource = strsql
[tab]End If

End Sub

Well, I don't think I got all of it, but try this and Let the world know WHAT error you get WHERE!




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[OC2]

Hi MichaelReed,

Thx for your response, it didnt work, but it helped me make it work

this is the working code


Private Sub Command2_Click()
Dim db As Database
Dim rst As Recordset

Dim crdate
Dim strsql As String

crdate = &quot; #&quot; & Me!month & &quot;/&quot; & Me!day & &quot; / &quot; & Me!year & &quot;# &quot;
strsql = &quot;Select * from [sheet1] where&quot; _
& &quot; [date]= &quot; & crdate

Set db = CurrentDb()

Forms![Form].RecordSource = strsql


End Sub

with the Dim crdate as Date included the crdate = 12:00 AM
so i removed it and added the #

thx a lot for you help though