What's wrong with my Start & End Date for user to key in? It's not working..

[awinnn]

Hi,
I have txtAssetType, txtStartDate and txtEndDate on the form. I wan
user to select for [Asset Type] and enter start and end date for [Issu
Date]. This is what i've done,


Code
-------------------

Dim bWhereClaus As String

bWhereClaus = "[Asset Type] = '" & Me.txtAssetType & "' and
[Issue Date] BETWEEN #" & Format
(Me.txtStartDate, "mm/dd/yyyy") & "# AND #" & Format
(Me.txtEndDate, "mm/dd/yyyy") & "#"

DoCmd.OpenReport "rptName", PrintMode, , bWhereClaus

-------------------


but..it doesn't work..the report doesn't come out..any idea?

thanx in advance..:

 

[Allen Browne]

The idea is right. Suggestions:

1. Assuming txtStartDate and txtEndDate are unbound, try setting their
Format property to Short Date so Access knows to interpet them as date
values.

2. The code will fail if the text boxes are left Null, so try checking for
Nulls with IsNull() if that is the problem.

3. If Issue Date is a calculated field in a query, wrap the calculation in
CVDate() to ensure Access interprets it as a date.

4. Are you displaying 4-digit years? Any chance of a century mismatch?

5. Is there a time component in the date/time field? This would cause values
on the final date to be excluded.

6. Is Asset Type actually a Text field? If it's a Number type field, drop
the extra quotes, and make sure you are supplying a numeric value.

 

[Albert D. Kallal]

Your string is not formed right. Did you even try to compile the code?

Does your code compile ok without errors?

I would try:

Dim bWhereClaus As String

bWhereClaus = "[Asset Type] = '" & Me.txtAssetType & "'" &_
" and [Issue Date] BETWEEN #" & _
Format(Me.txtStartDate, "mm/dd/yyyy") & _
"# AND #" & _
Format(Me.txtEndDate, "mm/dd/yyyy") & "#"

DoCmd.OpenReport "rptName", PrintMode, , bWhereClaus

Another thing to try is right before the openreport, put a:

msgbox bWhereClause
debug.Print bWhereClause

You will then see what the where clause looks like, and then when it works,
you can remove the command. You can also cut and past the results from the
debug window into a message for this newsgroup, and we can probably point
out what is wrong with the where clause.