something is wrong with my criteria

[beanbeanbean]

hey guys would you pls help me.


i am currently doing a search form. and i am working on how to extract dates in between.


for example. i have an option group and i set the start date and the end date. and then when i press the search button, it should call out every date in the table that is in between the dates i set.

currently i'm using

Between [Forms]![Search Form]![QStart] And [Forms]![Search Form]![QEnd]

but it does not seem to work !

i'm trying to extract the dates from the table field transaction date 1, transaction date 2, transaction date 3 and date of query.

i pasted Between [Forms]![Search Form]![QStart] And [Forms]![Search Form]![QEnd] in the criteria of each one of them but it does not seem to work.

can anyone help me ! thanks.


the end product of the search button should be that when i set the dates and press the search button, the full table would come out showing every field but only the ones that have the dates which are in between the set dates.



attached is my database. would be deeply grateful.

 

[pteare]

You could making your code run a query to do that. The query would normally display your full table, but the code would pop in a where clause on the dates.

Sorry I'll can't download your database (I know nothing of viruses..)

Lets say your table is called tblTable, and make a query showing everything in that table called qryTable.

Dim qdf As DAO.QueryDef
Dim strSQL as String
Dim strStartDate as String
Dim strEndDate as String
 
Set qdf = CurrentDb.QueryDefs("qryTable")
 
'I'm assuming you've two text boxes to get the dates from:
strStartDate = Format(Me!txtStartDate, "mm\/dd\/yyyy")
strEndDate = Format(Me!txtEndDate, "mm\/dd\/yyyy")
 
 
strSQL = "SELECT tblTable.* FROM tblTable " & _
            "WHERE tblTable.date > " & strStartDate & " AND tblTable.date < " & strEndDate & ";"
 
qdf.SQL = strSQL
 
DoCmd.OpenQuery ("qryTable")

hope that helps / works!

 

[namliam]

If I search for january (starting 1/1 ending 1/31) it works perfectly in returning 3 records?? Not sure what is wrong here.

 

[pteare]

sorry I don't understand what is not working?

 

[beanbeanbean]

hi namlian. try search from 1-jan to 9-jan. the 20-jan still pops out. why is that so ?

and there are more than 3 records between 1-jan to 31-jan in all 4 fields transaction date 1 , transaction date 2, transaction date 3 and date of query.

 

[namliam]

hi namlian. try search from 1-jan to 9-jan. the 20-jan still pops out. why is that so ?

Because all your fields are TEXT fields, none of your date fields are actually dates....

In text you have this order
1/1
1/10
1/11
...
1/2
1/20
1/22
...
1/3
1/4
...
1/9
Not the "real" date order. You have to change your dates to actual date fields.

 

[beanbeanbean]

doh ! stupid me. omg thank you very much !!