UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Where Condition Using A Date, Access 2010    
 
   
armadillo
post May 12 2017, 07:33 PM
Post#1



Posts: 141
Joined: 4-August 14



For a report called drgDrugVialUseHistories, the record source has a field called DateReceived. For one record the value for DateReceived is 12/21/2015. For all other records the value is 6/29/2015. I want to open the report to view records in which the DateReceived is after a date selected by a user. However, even if the date selected by the user (currently from an input box..but I am open to other ideas on how best to get user input) is after the DateReceived value (say by entering '6/30/2015'), the report opens to show all the records (i.e, not just the single one with a DateReceived of 12/21/2015). Here is the code I have on a form's button to open the report:

CODE
Private Sub cmdUseHistoryAllVials_Click()
Dim SelectDate As String
SelectDate = InputBox("Received after what date? (enter as XX/XX/XXXX)", "Select a date")

    If IsDate(SelectDate) = True Then
        If SelectDate < #8/26/2013# Then
            MsgBox "Use history is available only for drugs received after 8/26/2013"
        Else
            If DateDiff("d", CDate(SelectDate), Date) < 0 Then ' I tried using if SelectDate > Now() here, but that did not work
                MsgBox "There have been no deliveries from the future"
            Else
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'This the the piece of code that is not working properly
                DoCmd.OpenReport "drgDrugVialUseHistories", acViewPreview, , "DateReceived >" & CDate(SelectDate)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            End If
        End If
    Else
        MsgBox "Enter the date in the correct format (i.e., 'XX/XX/XXXX')"
    End If
End Sub


I must be overlooking something simple, but I've been staring at this long enough that I thought it was time to ask for some help.
Go to the top of the page
 
theDBguy
post May 12 2017, 08:44 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


To give you a clue as to what may be happening, after the report opens, type the following in the Immediate Window:

?Reports!drgDrugVialUseHistories.Filter

What do you see?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
armadillo
post May 13 2017, 01:12 AM
Post#3



Posts: 141
Joined: 4-August 14



After entering '12/20/2015' into the input box, opening the form and typing

?Reports!drgDrugVialUseHistories.Filter

into the Immediate Window, the result is:

DateReceived >12/20/2015

Which is what it should be, correct? Yet the report is still displaying records with dates prior to 12/20/2015.
Go to the top of the page
 
cheekybuddha
post May 13 2017, 05:07 AM
Post#4


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


Hi,

Do a search of UA for 'date delimiter in Access'.

Try substituting your line with:
CODE
' ...
                DoCmd.OpenReport "drgDrugVialUseHistories", acViewPreview, , "DateReceived > " & Format(SelectDate, "\#yyyy\-mm\-dd\#")
' ...


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
cmohanc
post May 13 2017, 05:40 AM
Post#5



Posts: 47
Joined: 8-May 16



Hi ,

try this

Convert Selectdate into a number , using clng( SelectDate)
Then use the filter condition on the number.
It shall work properly.
This post has been edited by cmohanc: May 13 2017, 05:41 AM
Go to the top of the page
 
armadillo
post May 13 2017, 11:22 AM
Post#6



Posts: 141
Joined: 4-August 14



cheekybuddha, thanks your line works. I have some follow up questions/observations though.

I had to do something similar on this line, using CDdate() to convert to a date, when testing whether the date input by a user was a future one.

CODE
If DateDiff("d", CDate(SelectDate), Date) < 0 Then ' I tried using if SelectDate > Now() here, but that did not work


but strangely enough, CDate() did not work on the line of code I first posted about, although your suggestion of Format(SelectDate, "\#yyyy\-mm\-dd\#") did.

CODE
DoCmd.OpenReport "drgDrugVialUseHistories", acViewPreview, , "DateReceived >" & CDate(SelectDate)


My questions are:

1. Why should I need to use other functions [ie., Cdate() and Format()] to coerce the variable into a date at all? I thought by using the IsDate() function to test whether it is a date in the first place would make later conversion unnecessary. Apparently, IsDate() does not do what I think it does.
2. What is the difference between CDate(SelectDate) and Format(SelectDate, "\#yyyy\-mm\-dd\#")? They seem like the same thing, although clearly they are not interchangeable with each other.
3. It seems strange to me that within this subroutine, I am using 3 different functions related to dates: IsDate(), CDate(), and Format(). Are they all necessary?

Here is the working version:

CODE
Private Sub cmdUseHistoryAllVials_Click()
Dim SelectDate As String
SelectDate = InputBox("Received after what date? (enter as XX/XX/XXXX)", "Select a date")

    If IsDate(SelectDate) = True Then
        If SelectDate < #8/26/2013# Then
            MsgBox "Use history is available only for drugs received after 8/26/2013"
        Else
            If DateDiff("d", CDate(SelectDate), Date) < 0 Then ' I tried using if SelectDate > Now() here, but that did not work
                MsgBox "There have been no deliveries from the future"
            Else
                DoCmd.OpenReport "drgDrugVialUseHistories", acViewReport, , "DateReceived > " & Format(SelectDate, "\#yyyy\-mm\-dd\#")
            End If
        End If
    Else
        MsgBox "Enter the date in the correct format (i.e., 'XX/XX/XXXX')"
    End If
End Sub

Go to the top of the page
 
theDBguy
post May 13 2017, 11:32 AM
Post#7


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I'll let cheeky answer your questions, but regarding this:

QUOTE
After entering '12/20/2015' into the input box, opening the form and typing

?Reports!drgDrugVialUseHistories.Filter

into the Immediate Window, the result is:

DateReceived >12/20/2015

Which is what it should be, correct? Yet the report is still displaying records with dates prior to 12/20/2015.

I always say what our (humans) eyes see may not be the same as what the computer "sees." In this situation, when you see "DateReceived > 12/20/2015," you immediately understands 12/20/2015 is a date value. However, to the computer, 12/20/2015 is more like "12 divided by 20 divided by 2015" and the result is compared to the value in the field DateReceived. Since the result of the division is probably less than 1 or 0, then all date values in your table would be greater than the result of the division, which means all records will be returned or displayed by the report.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th November 2017 - 09:02 AM