Databse searches by date with sql

[coyote69]

I am trying to search an access database for a range of appointment date it gives me an error or it send the date back that are not within the range.
Dim Searchbeg As String
Dim searchend As String
frmresults.Cls


'Orders Records by appointment
Searchbeg = (InputBox("Enter beginning date of search&quot)
searchend = (InputBox("Enter end date of search&quot)
Searchbeg = Format(Searchbeg, "short Date&quot

datContactManager.RecordSource = "Select * FROM ContactManager ORDER by apptdate Asc"



datContactManager.RecordSource = &quot;Select * FROM ContactManager WHERE [apptdate] > = '&quot; & Searchbeg & &quot;' and <='&quot; & searchend & &quot;'&quot;

datContactManager.Recordset.FindFirst(&quot;apptdate&quot = Searchbeg '
datContactManager.Refresh
Call printsrch2

 

[Imhoteb]

Hi,

You use dao code to retrieve your records. Be sure you don't use ado or this code won't work.

Your sql formula is not in the right format. To search for dates you have to use the # character in stead of the '
You also must refresh your recorset before you go to the first record

You also should use the U.S. date format (month-day-year) when you search for fields containing dates, even if you're not using the U.S. version of the Microsoft Jet database engine; otherwise, the data may not be found. Use the Visual Basic Format function to convert the date.


datContactManager.RecordSource = &quot;Select * FROM ContactManager WHERE [apptdate] > = #&quot; & Searchbeg & &quot;# and <=#&quot; & searchend & &quot;#&quot;
datContactManager.Refresh

datContactManager.Recordset.FindFirst &quot;apptdate =#&quot; & Format(Searchbeg,M-D-YYYY) & &quot;#&quot;
Call printsrch2


hope this helps
Good luck

 

[coyote69]

I still get an error at the datcontactmanager.refresh line

 

[Imhoteb]

What kind of an error do you get?

What kind of recordset is datContactManager based on?
Did you set it as table recordset or as a dynaset recordset?

 

[coyote69]

I new at this vb stuff as you can tell. I did not set it as any of the two. the data base was built in access and I am accessing the access table named contact manager. when it does the search the datcontactmanager.refresh is highlighted. as an error

 

[Imhoteb]

I'm confused here.

Are you doing this in VB or are you programming in access?
If you are working in VB how are you connecting to your access database?
Are you doing it in code or do you use a datacontrol wich is connected to your table?

 

[coyote69]

It is in vb using the databound controls.

 

[Imhoteb]

ok,

There is another fault in the sql statement




datContactManager.RecordSource = &quot;Select * FROM ContactManager WHERE [apptdate] > = #&quot; & Searchbeg & &quot;# and [apptdate] <=#&quot; & searchend & &quot;#&quot;
datContactManager.Refresh