Problem running query, especially with dates

[josephbupe]

Hi,
I have two forms (frmMAIN and frmPersonsSub) that open side by side. I have field on the main form to query the second form (frmPersonSub), but I am having trouble with query or not querying with the date fields. What I have noticed is that I can run a few queries successfully but oftein I get the following error:

Run-time error 3075
Syntax error (missing operator) in query expression '([DateAdded] between AND)'

Please, help me resolve this with attached sample mdb file am working on.

Thanx.

Joseph

 

[namliam]

Your code is only checking for Isnull, when ever checking this you should also check for empty string or... alternatively change null to empty string using NVL

THus instead of:
Isnull(Yourfield)
use
NVL(Yourfield,"") = ""

 

[josephbupe]

Your code is only checking for Isnull, when ever checking this you should also check for empty string or... alternatively change null to empty string using NVL

THus instead of:
Isnull(Yourfield)
use
NVL(Yourfield,"") = ""

Hi,

That did not work either. I though the code caters would take care of that by including in it the If Not IsNull part in my initial code:

If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then  'End date, but no start.
            varWhere = "(" & strField & " <= " & Format(Me.txtEndDate, conDateFormat) & ") "
        Else
            'neither start nor end dates, do nothing to varWhere
        End If
    Else
        If IsNull(Me.txtEndDate) Then  'Start date, but no End.
            varWhere = "(" & strField & " >= " & Format(Me.txtStartDate, conDateFormat) & ") "
        Else 'Both start and end dates present.
            varWhere = "(" & strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
            & " AND " & Format(Me.txtEndDate, conDateFormat) & ")"
        End If
    End If

Joseph

 

[vbaInet]

I haven't looked at your db but I imagine you're building the sql string in code. What you need to do to spot the problem is to put a Debug.Print line to print the sql string to the Immediate Window so you that you can spot where you're going wrong.

Your problem is most likely related how you're building the string.

 

[namliam]

The error message: '([DateAdded] between AND)'

Indicates there is no value for your Me.txtStartDate, this can only happen if
1) the field is NULL
This is not the case cause that should be caught by ISNULL
2) The field is an empty string
As I indicated.

Also when your using dates, the dates should be
1) In US or ISO format
I am assuming the conDateFormat contains either MM/DD/YYYY or YYYY-MM-DD
2) Must be in ##
i.e. varWhere = "(" & strField & " <= #" & Format(Me.txtEndDate, conDateFormat) & "#) "

Lastly,

I though the code caters would take care of that by including in it the If Not IsNull part in my initial code

This indicates that you do not understand the difference between an empty string and a NULL value.
It also points out you are misunderstanding your own code.

The error message is about the BETWEEN part, which requires IsNull(Me.txtStartDate) to be FALSE, where as your NOT ISNULL contruct requires same to be TRUE.

The basic of a NULL is that anything you compare it too will return FALSE. Even NULL = NULL will return false. Only way to handle NULL is to use the IS NULL or ISNULL contruct/function.

Empty string is not null and is not trapped by ISNULL, becuase that is not null.... Not ISNULL("") will return TRUE, instead of the false you are expecting.

 

[josephbupe]

Hi,

I wish I could do much more than what I have.

Thanx.

Joseph

 

[vbaInet]

joseph, I've just quickly gone through your code and tidied it up plus added a few bits too. You may need to do more cleaning but I've given you an idea on how to approach it (which compounds upon what namliam has already mentioned).

 

[josephbupe]

joseph, I've just quickly gone through your code and tidied it up plus added a few bits too. You may need to do more cleaning but I've given you an idea on how to approach it (which compounds upon what namliam has already mentioned).

Hi,

I needed just to replace my Else 'Both start and end dates present with
ElseIf IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then 'Both start and end dates present.

Thank you so much for your efforts.

Stay well.

Joseph

 

[vbaInet]

Glad we could help.