Struggling with If then Else Date

fst

Registered User.
Local time
Today, 06:40
Joined
Apr 9, 2018
Messages
46
[SOLVED]Struggling with If then Else Date

I can't believe i'm struggling over something that looks so simple but :banghead::banghead::banghead:

Code:
Function Check_BA_Local()
strSQL = qry_ChkDate

    If strSQL = Date Then
    
        MsgBox "works"
        Else
        MsgBox "not today"
    End If
   End Function
where strSQL is a query that pulls todays date 5/30/2019
when I run this I get the "not today" when technically it should be "today" as the message correct? when I put ?Date in the immediate box I also get today's date.
 
Last edited:
Try this and see what you get
Code:
Dim strSQL  as Date
strSQL = qry_ChkDate
    debug.print qry_ChkDate
    If strSQL = Date Then
    
        MsgBox "works"
        Else
        MsgBox "not today"
    End If
 
Hi. Try using the DLookup() function to pull the value from the query.
 
I still get the msgbox "not today"
the data type in the table for the date is Date/Time if that matters?
 
I still get the msgbox "not today"
the data type in the table for the date is Date/Time if that matters?

Hi. Did you try it this way?
Code:
If DLookup("QueryField","QueryName")=Date Then
 
Hi. Did you try it this way?
Code:
If DLookup("QueryField","QueryName")=Date Then

you the man :D

Code:
Dim strSQL  As Date
strSQL = qry_ChkDate
    
    If DLookup("Date", "qry_ChkDate") = Date Then
    
        MsgBox "works"
        Else
        MsgBox "not today"
    End If
curious on why DLookup would work but not the simpler idea?
 
You can't get a single field value from a qry like you tried.

If you look in the immediate window I bet the debug.print was null.

Code:
debug.print "My Result :" & qry_ChkDate
 
If strSQL = Date Then

You have a query but this method doesn't run it. You are comparing the SQL statement to a date when using this syntax. I'm going to assume that qry_ChkDate is the actual name of the query. I hope that name works as a valid name.

There is ALSO the question of what is in the table underlying that query. How did that value get there? The point of my question is that if there is ANY CHANCE that the Date/Time field actually contains a time as well as a date, then you have 1/86,400 chance of getting a match, since the return value from the Date() function WILL NOT contain a time.

You MIGHT get somewhere like this:

Code:
Function Check_BA_Local()
Dim strSQL as String
Dim rsSQL as DAO.Recordset
Dim dtVal as Date

Set rsSQL = CurrentDB.OpenRecordset( "qry_ChkDate", dbOpenDynaset )
rsSQL.MoveFirst
dtVal = rsSQL.[Date-field-name]      'you supply the name of the field here
rsSQL.Close

'strSQL = qry_ChkDate

    If dtVal = Date Then
    
        MsgBox "works"
        Else
        MsgBox "not today"
    End If
   End Function

OR use the DLookup as suggested by theDBguy.

Code:
Function Check_BA_Local()
strSQL = DLookup( "[date-field-name-goes-here"], "qry_ChkDate" )

If strSQL = Date Then
    
        MsgBox "works"
        Else
        MsgBox "not today"
    End If
   End Function

Finally, for that code you showed us to have half-a-chance to work, I must infer that you are running without Option Explicit. You can do that, but the first time you ever spell a variable name incorrectly, you will take forever to find the error because in that mode of operation, VBA will simply create a variant-type variable for the new spelling, but it will have an "empty" value (i.e. the IsEmpty(variable-name) function would be TRUE.) Using Option Explicit, you must declare all of your variables before 1st use BUT if you spell something wrong, VBA will immediately call you out when you try to compile the code.

EDIT: Obviously theDBguy got there before I did. I'm having a strange slowdown on my system so he beat me to the response.
 
hmm ok, thanks for the in depth reason why the original thought I had wouldn't had worked at all. yes, qry_ChkDate is the name of the query and it is pulling from a local table with just the date with no time included

edit-how would the code look if add in a loop where after the else msgbox "not today" to go back and check after a certain duration at the query until the condition is met?
 
If DLookup("Date", "qry_ChkDate") = Date Then

why not try

msgbox (DLookup("Date", "qry_ChkDate") ) first, to see what you ARE getting.

also if your query returns more than 1 record, it will give you a random-ish result, which may or may nor be the one you expect.
 
edit-how would the code look if add in a loop where after the else msgbox "not today" to go back and check after a certain duration at the query until the condition is met?
Hi. There are a couple of ways you can approach that. If you keep the loop inside the function, then the program will hang until the condition is met. However, if you simply want to check the date while the user continues to work, then you would do it outside of the function.
 
fst, you certain COULD build a loop that go back and check for a condition repeatedly. However, because Access is interpretive and thus not very welcoming for multi-threaded operation, your loop would lock up the system.

There are such things as form timers that cause a form's OnTimer event routine to be fired, and you can do tests in such routines. How you would notify yourself or your user is a matter of what you need to do. This would be one example of theDBguy's suggestion to do a test externally.

The problem with "after a certain duration" is that while it is possible, Access is not very good at sitting around and just waiting for events other than user input. It is inefficient at waiting. Not that it can't... it just is not that good.
 

Users who are viewing this thread

Back
Top Bottom