DCount doesn't recognize data within a database table.

I have a database that runs an import of one CSV file and two xls files every day. I had set up fail-safes within the code to make sure that no blank files were imported, but recently discovered that this had caused some problems.

If DCount("*", "Table", "[Field]=Format('" & Activity_Date & "', 'Short Date')") = 0 Then
    MsgBox "Import brought over Zero files for Date imported. Please confirm this is accurate", vbYesNo, "Zero Import"
    If LResponse = vbNo Then
        Me.ByDate.SetFocus
        Exit Sub
    ElseIf LResponse = vbYes Then
Else

Above is the code that I created. Even if there is data in the table that is being searched, the database instead jumps to the Message Box line and then takes me to the bottom of the code that ends the import prematurely. So far, the only way that I've found to allow the import to go through completely is to turn off this section of the code completely. However, that means that there is no fail-safe in place if the import tries to bring in an empty CSV file. If there is any way to set this up, or maybe an easier way to check for blank CSV files, that would be greatly appreciated.
rosemary fletcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Is the field [Field] a date/time field?

Does it work if you try this?
If DCount("*", "Table", "[Field]=#" & Format(Activity_Date, "Short Date") & "#" ) = 0 Then
    MsgBox "Import brought over Zero files for Date imported. Please confirm this is accurate", vbYesNo, "Zero Import"
    If LResponse = vbNo Then
        Me.ByDate.SetFocus
        Exit Sub
    ElseIf LResponse = vbYes Then
Else

Open in new window

0
ste5anSenior DeveloperCommented:
The problem is obvious the date format.

Activity_Date is declared as Date?
You need to use the US date/time format, when building SQL strings, thus using #mm/dd/yyyy#.

[Field]=Format(" & Format(Activity_Date, "\#m\/d\/yyyy#") & ", 'Short Date')

Open in new window


And when [Field] is also a Date/Time then it's simply:

[Field]="& Format(Activity_Date, "\#m\/d\/yyyy#")

Open in new window


And last, but not least: are time portions involved? Then you need between checks.
0
rosemary fletcherAuthor Commented:
Sorry, I wasn't very clear. Yes, the field is a date format. The line "Activity_Date" is just the code I use for plugging in a relation to a textbox on a form that provides the date. The Activity_Code line looks like this:

Activity_Code=Me.ByDate.

The only time dates involved are through days, so the Me.ByDate textbox is all that is needed for the imports.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NorieVBA ExpertCommented:
If the format of the date is the problem then I usually find using the ISO date format yyyy-mm-dd to be a good idea.
If DCount("*", "Table", "[Field]=#" & Format(Activity_Date, "yyyy-mm-dd") & "#" ) = 0 Then
    MsgBox "Import brought over Zero files for Date imported. Please confirm this is accurate", vbYesNo, "Zero Import"
    If LResponse = vbNo Then
        Me.ByDate.SetFocus
        Exit Sub
    ElseIf LResponse = vbYes Then
Else

Open in new window

0
rosemary fletcherAuthor Commented:
@Norie
I tried the code that you offered, but the exact same thing happened.
0
NorieVBA ExpertCommented:
Rosemary

What happens if you 'hard-code' a date into the DCount?
0
ste5anSenior DeveloperCommented:
@Norie: Using the ISO date is not safe in Access.
0
rosemary fletcherAuthor Commented:
Using a solid date doesn't work either. It seems like the DCount just isn't working.
0
ste5anSenior DeveloperCommented:
Please post a sample database. Otherwise it's just guessing.
1
NorieVBA ExpertCommented:
Rosemary

What exactly did you try for the 'solid' date?

PS Does the values in the field have times as well as dates?
0
Gustav BrockCIOCommented:
Using the ISO date is not safe in Access.

That you have misunderstood. In fact, it's the only safe format as it won't fail with ADO.
1
rosemary fletcherAuthor Commented:
The date that I used as 3/15/2018 and no, the values in the field are dates only.

@ste5an: I am a bit uncomfortable posting a sample database to the internet as it contains important information about the company I am working for.
0
Gustav BrockCIOCommented:
It should read:

DCount("*", "Table", "[Activity_Code] = #" & Format(Me.ByDate.Value, "yyyy\/mm\/dd") & "#" ) = 0 Then

Open in new window

0
ste5anSenior DeveloperCommented:
You should post a sample. Not live data. Craft one.
0
ste5anSenior DeveloperCommented:
@Gustav: Got me with that..


That you have misunderstood. In fact, it's the only  safe format as it won't fail with ADO.
I don't use ADO that much :)

To be precise: The US format is the only safe format for Jet/ACE.
0
rosemary fletcherAuthor Commented:
@Gustav Brock: The data within the table is read in a short date format of mm/dd/yyyy so I am unsure how your code would help with the data reading.

@ste5an: Does this help?
Test.accdb
0
Gustav BrockCIOCommented:
The US format is the only safe format for Jet/ACE.

Stefan, I don't know where you got this from. The ISO format is absolutely safe with DAO, and as the only safe format for ADO, that makes it the only universal safe format in VBA and Access SQL. Add to this, that it the default format in SQL Server, so you can just as well make it a habit to use that format throughout.
One thing less to care about.
0
Gustav BrockCIOCommented:
The data within the table is read in a short date format of mm/dd/yyyy

That doesn't matter. The format is for display only and doesn't influence on the values.

so I am unsure how your code would help with the data reading.

It seems like you have mixed up the field name  and your control name.
0
ste5anSenior DeveloperCommented:
For The Source:

http://www.access.mvps.org/access/datetime/date0005.htm

and as far as I can remember it was also mentioned in the original Jet specs. Maybe this has changed over the years.
0
PatHartmanCommented:
To be precise: The US format is the only safe format for Jet/ACE.
It isn't Jet/ACE.  It is the SQL standard that assumes US date format for string dates.  If you are not building the query in VBA, you would NEVER format the date.  So:

Select ... From ... Where SomeDate = Forms!yourform!yourdate

Works fine as a querydef provided the "yourdate" control is bound to a datetime data type OR has a format property that indicates it is a date.

Whereas if you are building an SQL STRING in VBA, you MUST enclose the date in #'s AND the date MUST be mm/dd/yyyy or the non-ambiguous yyyy/mm/dd

strSQL = "Select ... From ... Where SomeDate = #" & Format(Forms!yourform!yourdate, "mm/dd/yyyy") & "#"

@Rosemary,
Open the table in design view.  Look at the definition for the date column.  Does it have a Format property?  If it does, REMOVE the format.  This may be masking the actual data in the table.  Now open the table and look at the data in the column.  Does any of it contain a time element?  If it does, that is your problem.

To fix it, you have to decide whether you actually want time in the date, in which case you would fix this query to search on just the date parto of the field or if you don't want time, then you would fix the data (one time) AND find the code that is using Now() and replace it with Date().  Now() = date AND time.  Date() = just date.

NEVER use formats on dates in the table.  All this does is to mask the actual data so this isn't helping them.  It is only hurting you.  Your users will NEVER see actual table data.  If you want to format it for display, do the format in the form.
1
rosemary fletcherAuthor Commented:
Ok, I tried removing the format from the field, but the same thing is still happening. And there is no time element in the data.
0
PatHartmanCommented:
Then you are going to have to post the table if you need to have us figure out the problem.  Disguise the sensitive stuff but leave the dates alone.
0
Gustav BrockCIOCommented:
Then go back to the table and post a list of field names and data types.
0
rosemary fletcherAuthor Commented:
Ok, I have reuploaded the test database with a new table containing data one day that has been imported. The data is in a new table called Date Data.
Test.accdb
0
Gustav BrockCIOCommented:
You need a correction of these two:

rst = DCount("*", "[FlagDate]", "[Activity_Date] = #" & Format(Me!ByDate.Value, "yyyy\/mm\/dd") & "#")

If DCount("*", "Table2", "[SpecH_ShipDate] = #" & Format(Me!ByDate.Value, "yyyy\/mm\/dd") & "#") = 0 Then

Open in new window

0
ste5anSenior DeveloperCommented:
First of all: Use Option Explicit in each module (standard or code behind). Add it manually where it is missing and activate it in the VBA IDE under Tools\Options, set Require Variable Declaration.

 This works with the latest sample:

Option Compare Database
Option Explicit

Private Sub Import_Click()

  Dim ActivityDate As Date
  Dim RecordCount As Long

  ActivityDate = Me.ByDate.Value
  RecordCount = DCount("*", "[FlagDate]", "[Activity_Date] = " & SqlDateJet(ActivityDate))
  If RecordCount <> 0 Then
    MsgBox "[FlagDate] > 0: " & RecordCount
  Else
    MsgBox "[FlagDate] = 0: " & RecordCount
  End If

  RecordCount = DCount("*", "[Date Data]", "[SpecH_ShipDate] = " & SqlDateJet(ActivityDate))
  If RecordCount <> 0 Then
    MsgBox "[Date Data] > 0: " & RecordCount
  Else
    MsgBox "[Date Data] = 0: " & RecordCount
  End If

End Sub

Public Function SqlDateJet(ADate As Variant) As String

  On Local Error GoTo LocalError
  
  SqlDateJet = Format(ADate, "\#m\/d\/yyyy#")
  Exit Function
  
LocalError:
  SqlDateJet = Format(Now, "\#m\/d\/yyyy#")
  
End Function

Private Sub Refresh_Click()
    Me.ByDate = ""
    Me.ByDate.SetFocus
    Me.Refresh
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Dates are delimited with #'s NOT single quotes.

DCount("*", "Table2", "[Field]=Format(#" & Activity_Date & "#, 'Short Date')") = 0 Then
0
ste5anSenior DeveloperCommented:
Caveat: Variable declaration in VBA is different than in VB. In VBA we need explicit declaration of the  data type, otherwise it is Variant.

Dim strsql, strsql1 As String

Open in new window

means in VBA strsql is a Variang and strsql1 is a String.
0
rosemary fletcherAuthor Commented:
I feel that I should also clarify that I am working in Access 2007.
0
PatHartmanCommented:
The code won't run for me and I don't have time to futz with it.  Please correct the incorrect delimiters everywhere they are used and see if that solves your problem.
0
rosemary fletcherAuthor Commented:
Ok, I tried all the coding that has been offered. One didn't work at all, and the database doesn't recognize SqlDateJet as a proper procedure.
0
Gustav BrockCIOCommented:
Your syntax is quite off. When corrected as I posted, you will/can get this box:

DateCount.PNG
0
ste5anSenior DeveloperCommented:
SqlDateJet is a function I've posted in the code sample. You need to copy it.
0
rosemary fletcherAuthor Commented:
Hey, I figured out the code! I ended up combining a few of the solutions offered in order to create the code you see below. Now it moves on if there is already data in the table and produces the error message when there is not, which is what I wanted in the first place! Since ste5an's code provided most of the framework, I believe that I will label that as the Best Solution, with Pat Hartman and Gustav Brock's codes as assisted solutions. Please let me know if that works for everyone, and thank you all so much for your help!

ActivityDate = Me.ByDate
ActivityDate = Format(ActivityDate, "Short Date")
RecordCount = DCount("*", "Table2", "[SpecH_ShipDate] = #" & ActivityDate & "#")
If RecordCount = 0 Then
'If DCount("*", "Table2", "[SpecH_ShipDate]= #" & Format(Me.ByDate.Value, "mm\/dd\/yyyy") & "#") = 0 Then
    MsgBox "Import brought over Zero files for Date imported. Please confirm this is accurate", vbOKOnly, "Zero Import"
        Me.ByDate.SetFocus
        Exit Sub
Else
0
rosemary fletcherAuthor Commented:
Thank you all so much for your help! I couldn't have done this without you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.