Like Function using a Textbox

[Carly]

I am trying to get data out of a linked table (cannot modify) where the datatype is Date/Time.

When I type in 31/08/2001 in the criteria it doesn't bring any data out but if I type Like "31/08/2001*" it works, which is fine if I was going to type the date in each time manually but the query actually looks at a textbox on a form.

Is there anyway I can use the Like function with a textbox? I have tried Like [Forms]![Frontpage]![txt1stDate]* but it doesn;t work.

Regards
Carly

 

[Mile-O]

Like [Forms]![Frontpage]![txt1stDate] & "*"

 

[Carly]

This works fine,

Could I use this for Between....And.....?

 

[Mile-O]

No.

What is the rest of the text in your field? Is it a time to accompany the date?

If you want to use Between...And... then consider using DateValue() function on your string and the use the Between...And...

 

[Carly]

The rest of the text in the field is the time to accompany the date.

The textboxs which I am refering to have default values of the following:

[txt1stdate] =

=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) (i.e. The first day of the previous month)

[txt2nddate] =

=DateAdd("d",-1,DateSerial(Year(Date()),Month(Date()),1))

I am unsure what you mean by use the datevalue() function on my string

 

[Mile-O]

Try this as a criteria:

Between DateValue([Forms]![frmYourForm]![txt1stdate]) And DateValue([Forms]![frmYourForm]![txt2nddate])


where frmYourForm is replaced with your form's name...

 

[Carly]

Thanks that works brilliantly

 

[Mile-O]

Actually, just to be on the safe side - I spot a potential problem:

Between DateValue([Forms]![frmYourForm]![txt1stdate]) And DateAdd("s",-1,DateValue([Forms]![frmYourForm]![txt2nddate])+1)

 

[Mile-O]

Dates, when trimmed are effectively 29/08/03 00:00:00

If you were wanting all records for the month of July then:

the first criteria I gave would return you all records between:

01/07/03 00:00:00 And 31/07/03 00:00:00


Notice that any record after midnight on the 31st would not be included.

What I've done is basically added one day to the 31st, making it the 01/08/03 00:00:00 and then removed one second from that time making the final criteria:

Between 01/07/03 00:00:00 And 31/07/03 23:59:59