Multiple Criteria DCount - Date problem

**TheSmileyCoder** · Sep 10 '10, 08:18 AM

I dont immediatly spot anything wrong, so it may be related to the data you have in your table/form. In most cases where you have a long string concatation, especially those with form fields, the best way to debug is to do:

Code:

Dim strWhere as String
   strWhere="(([Field1]='" & Form_frmMain.cmbField1 & "') AND (" & "[Field2]='" & Form_frmMain.cmbField2 & "') AND (" & "[Field3]='" & Form_frmMain.cmbField3 & "') AND (" & "[Field4]='" & Form_frmMain.cmbField4 & "') AND (" & "[Field5]=#" & Form_frmMain.txtField5 & "#))") > 0 

'one of the below, don't have to do both
   Msgbox strWhere
   debug.print strWhere 'Goes to the immediate window

   Dcount("*","[tblConsensus]",strWhere)>0

If you still cannot see whats wrong, post the value of strWhere here for us to look at.

**NeoPa** · Sep 10 '10, 01:05 PM

You're in Italy Doma, so the chances are the default format for your dates displaying is not even recognised by Jet SQL. Strictly speaking, date values should always be formatted as m/d/yyyy when used as literals in a SQL string. See Literal DateTimes and Their Delimiters (#) for more on this.

**doma23** · Sep 12 '10, 01:55 PM

Smiley, tnx for the code. It might be useful in the future.
The problem was with date format.
I've formated data type of the "ReportDate " field in the database to m/d/yyyy. I've also formated the date control in control properties to m/d/yyyy. The problem was still there, I would still get the same error message, same sql string. Access would recognize all the values in the fields but it would say that there is a problem with syntax, especially referring to date control.
Although I changed the formats to m/d/yyyy, in sql string in error it would still be written 'd.m.yyyy.'
After that, I've changed the Windows date settings into a m/d/yyyy format, after which the code worked immediately.

The problem is that I still need to find the way to write the code so that it doesn't depend on users windows settings.
NeoPA, I've took a look on the link you provided, but I'm still trying to figure out how to make it work undependable on win settings.

**doma23** · Sep 12 '10, 02:05 PM

BTW. I've tried to use format function inside dcount, but it would just replace fractions (/) with dots (.), so now the date is recognized as "8.23.2010. " instead of "8/23/2010".

Code:

If Dcount("*", "[tblConsensus]", _
     "(([field1]='" & Form_frmMain.cbo1 & "') AND (" & "[field2]='" & Form_frmMain.cbo2 & "') AND (" & "[field3]='" & Form_frmMain.cbo3 & "') AND (" & "[field4]='" & Form_frmMain.cbo4 & "') AND (" & "[datefield]=#" & Format(Form_frmMain.txtDate, "m/d/yyyy") & "#))") > 0 Then

UPDATE:
In the database, I've changed the data type of the date field from date to text. Now it works and it seems ok. But I'm thinking if any problems could come out eventually from this setting (date field formatted as text)?

**NeoPa** · Sep 13 '10, 09:12 AM

The SQL standard is only there for use with SQL. It should not be allowed to interfere with, or depend on, any other settings.

Code:

DCount("*", _
       "[tblConsensus]", _
       "(([Field1]='" & Form_frmMain.cmbField1 & "') AND " & _
       "([Field2]='" & Form_frmMain.cmbField2 & "') AND " & _
       "([Field3]='" & Form_frmMain.cmbField3 & "') AND " & _
       "([Field4]='" & Form_frmMain.cmbField4 & "') AND " & _
       "([Field5]=#" & Format(Form_frmMain.txtField5, "m/d/yyyy") & "#))") > 0

NB. If the code is running within Form_frmMain then it can be referred to more simply as Me.

**doma23** · Sep 13 '10, 09:25 AM

I understand and it seems logical, but like I've said, it just replaces fractions (/) with dots (.) and it doesn't work.
The date is recognized as "8.23.2010. " instead of "8/23/2010" how it should be recognized accoring to sql format function I've used.

In a way I've solved it by putting field data type to Text instead of Date in database. It's a partial solution, and I don't know whether it's good and if I can have any problems later.
Since the values are entered in database solely by using form, and on form the text control txtDate is formated as Date, there is no way non valid date can be inserted in database. I guess it should be ok.

PS. I know about "me", the code is runned outside Form_frmMain. But tnx. :-)

**NeoPa** · Sep 13 '10, 11:05 AM

That's somewhat strange. Can you post for me the results of the following command so that I can try to determine exactly what is happening and where.

At the point where the DCount() call is about to run, run the following instruction from the Immediate Pane and post the results in here for me please :

Code:

? Forms!frmMain.txtField5, _
  Format(Forms!frmMain.txtField5, "m/d/yyyy"), _
  Format(Forms!frmMain.txtField5, "d mmmm yyyy")

PS. It's generally better practice to use Forms("frmMain" ) or Forms!frmMain rather than Form_frmMain. It's actually possible to open more than a single instance of the form. This can be handled via the Forms collection but not by a simple class reference. Not a big deal in most cases, but it's code you'll come across more frequently for sure.

**doma23** · Sep 13 '10, 11:33 AM

This is the result:
13.9.2010.
9.13.2010
13 settembre 2010

**doma23** · Sep 13 '10, 12:03 PM

PS. I'm using "Form_frmMa in" because in this way after I put dot, it gives me the list of all properties for the form and all controls on the form, whereas with "Forms!frmM ain" or Forms("frmMain" ) this useful function is not enabled.

**NeoPa** · Sep 13 '10, 12:34 PM

That's a bit weird Doma. It seems it is not only expecting dots (.) as date separators, it is also converting the slashes (/) to dots (.) for you. Not great news for when dealing with SQL literal dates.

There is a reliable way around it, but I'm frankly shocked that there appears to be no way reliably to format a SQL date literal into a VBA string. The alternative would be to use "\#d mmm yyyy\#". This should not be affected by your locale settings and is also 100% unambiguous.

PS. Using Form_frmMain that way seems a good idea. I do it myself sometimes just to find the properties easily, but I don't typically leave it in the resultant code. As it works fine in most cases though, there's little reason not to.

**doma23** · Sep 15 '10, 03:26 PM

I've decided that formating database field to text instead of a date is not a satisfying solution.
I've ran on some troubles.
You can't format the text field in database to show figures in date format (i.e. "dd mm yyyy").
Since the combo box cboDate has the row source property that is populating the list from the tblConsensus.Re portDate field, and since this field is formatted as text, it doesn't forbids the input of the values which are not date in the combo box, while when ReportDate is formatted as Date it limits the input only to date values. Limit to list option is not a choice as I need combo box enabled for inputting new values different than already in the database.
Maybe it could be done through setting Validation Rules,but even if it's possible I find it kind of a messy.

So, the problem I ran into was that the user could insert dates in table in different formats using different separators (maybe according to local settings). Or that user could insert values that are not date at all.

The other thing I've stumbled upon regarding this is this quote from Access Bible:

"When working with dates, you're almost always better off storing the data in a Date/Time field than as a Text field. Text values sort differently then date data (dates are stored internally as numeric values), which can upset reports and other output that rely on chronological order."

Unfortunately, "\#d mmm yyyy\#" didn't work. I believe that the problem with this format is in the 'month' which is represented as a word, so it's influenced by local settings - in Italian it would be "Settembre" for "September" .

In the end, this is the solution I have implemented:
-I've formatted ReportDate in database as a Date/Time field with this format "dd/mm/yyyy". The "semi-universal" format is necessary, because if there is no format settings, Access would take local settings for date format, and I think there is a bug in Access localization settings conversion (in 2007, but probably in 2003 also).
After this, the combobox cboDate would transform the inputed dates to the local settings, in case format isn't set in combobox properties. But this doesn't matter much, since this again needs to be formatted in my DCount code.
This code formatted it correctly to "dd/mm/yyyy", in a way that SQL could understand:

Code:

DCount("*", "[tblConsensus]", _
     "(([Field1]='" & Form_frmMain.cmbField1 & "') AND (" & "[Field2]='" & Form_frmMain.cmbField2 & "') AND (" & "[ReportDate]=#" & Replace(Format(Form_frmMain.cboDate, "dd/mm/yyyy"),".","/") & "#))") > 0 Then

Here is why:
Intermediate window:

Code:

? Form_frmMain.cboDate
13.03.2010.
? (Format(Form_frmMain.cboDate, "dd/mm/yyyy")
13.03.2010
? Replace(Format(Form_frmMain.cboDate, "dd/mm/yyyy"),".","/")
13/03/2010

Even, if the database ReportDate was formatted differently, like 13-Sett-2010, it would still work good, as it would be converted to local settings from universal sql format. The problem lies in the conversion from local to sql format.

After I've implemented this, another cute solution came to my mind, it worked also, and it's more simple:

Code:

DCount("*", "[tblConsensus]", _
     "(([Field1]='" & Form_frmMain.cmbField1 & "') AND (" & "[Field2]='" & Form_frmMain.cmbField2 & "') AND (" & "[ReportDate]=#" & Format(Form_frmMain.cboDate, "dd mm yyyy") & "#))") > 0 Then

Intermediate window:

Code:

? (Format(Form_frmMain.cboDate, "dd mm yyyy")
13 03 2010

This was also recognized and it worked immediately.

**NeoPa** · Sep 16 '10, 12:35 PM

All clever and well reasoned stuff Doma.

The only thing I'd point out is that the SQL date format is not d/m/yyyy as you have it, but m/d/yyyy (as in the USA). As the linked article points out, you may not see this go wrong very often as various dates (including your example) are unambiguous. As soon as the 13 is seen it realises that it cannot be a month so interprets it correctly even though it's technically the wrong way around.

Otherwise sterling job to make so much good sense of a pretty complicated situation :)

Multiple Criteria DCount - Date problem

Multiple Criteria DCount - Date problem

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment