# for dates

Thanks Alex.

So...

Like "*" & "#" & >=[Forms]![AdminF]![Text_SearchDateStart] & "#" & "*" AND "*" & "#" & <=[Forms]![AdminF]![Text_SearchDateEnd] & "#" & "*" Or Is Null

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

I'm trying to do a date range in a dlookup. Could that be the issue here too? Or is this my evil half brother Double Double Quotes?

=Nz(DCount("*","AppTrackerT","ApptDate>=" & "Date()" And "
(It works until I add the AND bit at the end)

Also Tried

=Nz(DCount("*","AppTrackerT","ApptDate>=" & "Date()" And "<=Now()"),0)

Try this:
=Nz(DCount("*","AppTrackerT","ApptDate>=#" & Date() & "# And ApptDate<=#" & Now() & "#"),0)

Hi Dan, the correct syntax for checking if a value is within a range is:

    Something >= x AND Something <= y

It is not:

    Something >= x AND <= y

These are expressions, and they follow VBA rules.  You write them the same way you write VBA code:

   If Something >= x AND Something <= y Then
       ...
   End If

When the query design screen lets you write something like ">=x And <=y" (see picture below) -- which I suspect is where many students got this habit from -- it is only for the user's convenience.  It is not a valid Access expression whatsoever.  When you go to the actual SQL screen, which is what really counts, the correct syntax is automatically used: field >= x And field <= y.

Thanks Kevin and Kevin. Learned a lot from this.

1
~ "ApptDate>=#"         & Date() &          "# And ApptDate<=#"         &      Now()        &            "#"   ~

- The Field Name, Inequalities and #'s are all enclosed in quotes
- The Concats and Functions are not

2. Always define field names in follow up criteria.

Thanks again, i'll bookmark this and refer back from time to time!

Hmm... Looks right but i'm still getting 0
(I temporarily removed the Nz while diagnosing)

Any ideas?

Can you build a standalone query that gets the number you require?

Hi Dan, you get zero because Date() and Now() both return today's date, Jan-12-2023.  I'm not sure if that's the date range you want here.  To be exact, Date() returns the start of today, which was Jan-12-2023 12:00:00am, and Now() returns the exact date and time at this moment.  If your table doesn't have a date that fall between 12am and 6:40pm (my time right now) of today, you get no result with this code.

Also, the "#" sign inside DCount() is not really needed in this instance.  Date() and Now() are date values themselves, so they don't need "#".  You need "#" only when you need to convert something that is NOT a date, such as a string that looks like a date (E.g. "1-12-23", "1/12/23") into a date.  So, just write:

    DCount("*","AppTrackerT","ApptDate>=Date() And ApptDate<=Now()")

But again, you need to fix your date range in order to get a result.

Thank you fellas. Ill try this on Monday when i get back in to work. Life saver!

With regards to date/time values. Yes, I'm aware date() returns 00:00:01 of the day, what why Im using the range. Essentially trying to show everything booked for today so i figured >=Date() to <=Now() would encompass today 00:00:01 to the time that it is today.

Hi Dan, you will still get zero if the appointment time is later than Now().  Right now it is 9:13am, and if the appointment time is 9:14am or after, you'll get zero appointment.  If you need to see appointments for the entire day, this may not be the way.

Thanks Kevin, yes that's intentional. It's a tracker rather than a CRM so wanting to see which appointments were booked today (Like a sales stat), rather than when an appointment is due! Ranging from beginning of today to now is perfect

I'm trying to get it to count the number of records based on dates entered on the form. My head is swimming!

Tried keeping it simple (This shows total number of records)
=Nz(DCount("*","AppTrackerT","ApptDate>=" & [Text_SearchDateStart]),0)


Tried Kevin F's Approach Above (Parenthesis Error)
=Nz(DCount("*","AppTrackerT","ApptDate>=#" & [Text_SearchDateStart] & "# And ApptDate<=#" & [Text_SearchDateEnd & "#"),0)


Followed the DLookup example (#Error)
=Nz(DCount("*","AppTrackerT","ApptDate>=#""" & [Text_SearchDateStart] & """ And ApptDate<=""" & [Text_SearchDateEnd] & """"),0)

I can only hope that i'll get the hang of it some day. I've been trying at this for about 6 hours now (Minus Previous days!).

Please Help!!! Any recommendations for adding further criteria?

In the second:
Text_SearchDateEnd is missing a closing bracket.

In the third:
You are trying to compare a date to a string.

Thanks Kevin. Hasn't worked though.

Tried 2nd without the typo (Facepalm with a bag hammers) but the number is sporadic, not accurate to what we want (01/01/2023 To 05/01/2023 Showed 105 While 01/01/2023 To 16/01/2023 Showed 97)

Took another stab at third by adding the #'s but got lost with the double double double double double quotes!!!

=Nz(DCount("*","AppTrackerT","ApptDate>=#" & ""[Text_SearchDateStart] "" & "#" And "ApptDate<=#" & ""[Text_SearchDateEnd]"" & "#"")

Start with it hardcoded:
=Nz(DCount("*","AppTrackerT","ApptDate>=#?# And ApptDate<=#?#")
Then swap one and check it works
=Nz(DCount("*","AppTrackerT","ApptDate>=#" & [Text_SearchDateStart] & "# And ApptDate<=#?#")
Then the next
=Nz(DCount("*","AppTrackerT","ApptDate>=#" & [Text_SearchDateStart] & "# And ApptDate<=#" & [Text_SearchDateEnd] & "#")

What an original idea! Will give it a go. Ill try to do this more often when I'm struggling, thanks!

23 Days and i can finally get on to this! Alex's advice is working a treat - i'm working through it now and so far so good!

One Problem for us UKers. VBA is using American date format. The [Text_SearchDateStart] is a custom text box that the end users will type their requested date in in UK format.

I could resolve this by using a separate text box which converts the date but i was wondering if there is a cleaner way around this issue? Thanks all!

Use the Format function.

Format function is good for viewing, but can I use it in the VBA? The users will be inputting the UK date but the DCOUNT function is still treating it as US. So how would i implement format here?.....

=DCount("*","AppTrackerT","Status=59 And ApptDate>=#" & [Text_SearchDateStart] & "#")

If the UK date format is used try formatting the date to the US format in the function.

=DCount("*","AppTrackerT","Status=59 And ApptDate>=#" & Format([Text_SearchDateStart], "mm/dd/yyyy") & "#")

Thanks mate. Been having problems with dates since day one. This worked. Phew! I can work from this. Sent you a little something for your help cheers

You're welcome.