User Selected Date

[markbeserra]

Hi all,

I am having some difficulty in my project trying to allow a User to select records between dates.

I get the following error when two dates (StartDate) and (EndDate) are entered in to the text boxes and the query is run.

Run-time error '13'

Type mismatch

I don't know if the problem is in my code or if the problem exists in the date/time format.

The query will return the correct records when the following is entered directly into the criteria field of the query.

Between #3/19/2001# And #3/22/2001#

The code I'm using allows for the User to make multiple selections from other fields for the query (concatinating Where Statements). Specifically the code for the date text boxes I"m using is as follows:

If Not IsNull(Me![EndDate]) Then
where = where & " AND [TransactionDate] between #" + _
Me![StartDate] + "# AND #" & Me![EndDate] & "#"
Else
where = where & " AND [TransactionDate] >= #" + Me![StartDate] _
+ " #"
End If

I hope this passes the correct syntax for the Query.

Now for the Date/Time format, the table that is being queried formats the Date/Time as 'General Date'. When I directly enter a 'Short Date' format into the Query criteria field, a correct query returns. The Text boxes in which the User enters the date is formatted as 'Short Date'.
So, I'm not sure if the formatting is the problem here.

The other code works great for all other parts of this form except for the date portion.

Can anyone help?

Thanks,

Mbeserra

 

[Aivars]

Hi!
Point breakpoint one row after row which compose SQL for query, on Immediate window ask for result of SQL expression (like >>> ?strSQL), then copy result and paste into SQL window of query constructor. Try to ran qury, then analise errors.

Aivars

 

[markbeserra]

Aivars,

I'm still green to VB, can you give me more detail on how to carry out your suggestions.

Thanks

Mbeserra

 

[MichaelRed]

Public Function basChkDateWhere(StartDate As Date, Optional EndDate As Date) As String

    If (Not (EndDate = 0)) Then

        SqlWhere = SqlWhere & " AND [TransactionDate] "
        SqlWhere = SqlWhere & "between #" & CDate(StartDate)
        SqlWhere = SqlWhere & "# AND #" & CDate(EndDate) & "#"

     Else

       SqlWhere = SqlWhere & " AND [TransactionDate] >= # "
       SqlWhere = SqlWhere & CDate(StartDate) & " #"

    End If

basChkDateWhere = SqlWhere
End Function

Sample Results

? basChkDateWhere (#1/1/01#)
AND [TransactionDate] >= # 1/1/01 #


? basChkDateWhere (#1/1/01#, Date)
AND [TransactionDate] between #1/1/01# AND #5/24/01#


This can at least 'show' you what the string returned. I think the use of "Where may be a problem, aand the use of text boxes as the sources of the dates may not be acceptable.
MichaelRed
redmsp@erols.com

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

 

[markbeserra]

Michael,

Thanks.

I created the class module, Dim Sql As String.

Now how do I actually Use this thing?


Mbeserra

 

[MichaelRed]

Didn't really mean this as a production usage thing, just to let you EASILY see the string being created by your code. Also cannot specifically say " ... Now how do I actually Use this thing ... ". If you want to try it, then wherever the original If BLOCK was/is, replace with something like:

[tab]Sql = Sql & " " & basChkDateWhere(Me![StartDate], Me![EndDate]).

I think it will generally work, witht he probable exception of the extra spaces in the first Sample Results:

? basChkDateWhere (#1/1/01#)
AND [TransactionDate] >= # 1/1/01 #

# 1/1/01 #
^ ^ <==== the spaces indicated should not be there.


MichaelRed
redmsp@erols.com

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