473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Multiple Criteria DCount - Date problem

doma23
107 100+
Hi,

I have the following code that doesn't work:

Expand|Select|Wrap|Line Numbers
  1. DCount("*", "[tblConsensus]", _
  2.      "(([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
Field5 is formated as a date in database.
I got the run-time error 3075 (syntax error in date in query expression...) when I run this.

Thank you!
Sep 10 '10 #1

✓ answered by NeoPa

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.

12 4755
TheSmileyCoder
2,322 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere as String
  2.    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 
  3.  
  4. 'one of the below, don't have to do both
  5.    Msgbox strWhere
  6.    debug.print strWhere 'Goes to the immediate window
  7.  
  8.    Dcount("*","[tblConsensus]",strWhere)>0
If you still cannot see whats wrong, post the value of strWhere here for us to look at.
Sep 10 '10 #2
NeoPa
32,556 Expert Mod 16PB
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.
Sep 10 '10 #3
doma23
107 100+
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.
Sep 12 '10 #4
doma23
107 100+
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".

Expand|Select|Wrap|Line Numbers
  1. If Dcount("*", "[tblConsensus]", _
  2.      "(([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)?
Sep 12 '10 #5
NeoPa
32,556 Expert Mod 16PB
The SQL standard is only there for use with SQL. It should not be allowed to interfere with, or depend on, any other settings.

Expand|Select|Wrap|Line Numbers
  1. DCount("*", _
  2.        "[tblConsensus]", _
  3.        "(([Field1]='" & Form_frmMain.cmbField1 & "') AND " & _
  4.        "([Field2]='" & Form_frmMain.cmbField2 & "') AND " & _
  5.        "([Field3]='" & Form_frmMain.cmbField3 & "') AND " & _
  6.        "([Field4]='" & Form_frmMain.cmbField4 & "') AND " & _
  7.        "([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.
Sep 13 '10 #6
doma23
107 100+
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. :-)
Sep 13 '10 #7
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. ? Forms!frmMain.txtField5, _
  2.   Format(Forms!frmMain.txtField5, "m/d/yyyy"), _
  3.   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.
Sep 13 '10 #8
doma23
107 100+
This is the result:
13.9.2010.
9.13.2010
13 settembre 2010
Sep 13 '10 #9
doma23
107 100+
PS. I'm using "Form_frmMain" 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!frmMain" or Forms("frmMain") this useful function is not enabled.
Sep 13 '10 #10
NeoPa
32,556 Expert Mod 16PB
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.
Sep 13 '10 #11
doma23
107 100+
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.ReportDate 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:

Expand|Select|Wrap|Line Numbers
  1. DCount("*", "[tblConsensus]", _
  2.      "(([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:
Expand|Select|Wrap|Line Numbers
  1. ? Form_frmMain.cboDate
  2. 13.03.2010.
  3. ? (Format(Form_frmMain.cboDate, "dd/mm/yyyy")
  4. 13.03.2010
  5. ? Replace(Format(Form_frmMain.cboDate, "dd/mm/yyyy"),".","/")
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. DCount("*", "[tblConsensus]", _
  2.      "(([Field1]='" & Form_frmMain.cmbField1 & "') AND (" & "[Field2]='" & Form_frmMain.cmbField2 & "') AND (" & "[ReportDate]=#" & Format(Form_frmMain.cboDate, "dd mm yyyy") & "#))") > 0 Then
Intermediate window:
Expand|Select|Wrap|Line Numbers
  1. ? (Format(Form_frmMain.cboDate, "dd mm yyyy")
  2. 13 03 2010
This was also recognized and it worked immediately.
Sep 15 '10 #12
NeoPa
32,556 Expert Mod 16PB
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 :)
Sep 16 '10 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
3
by: psuaudi | last post by:
I am trying to use the following code, but I think there is an error in my syntax: Count = DCount("", "Change Dates", " = '" & !! & "' AND CVDate(Int( #" & & "# )) = #" & (Date) & "#") I...
3
by: developing | last post by:
Hello How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field) edit: this will be in the after...
4
by: JHNielson | last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria This is the string: EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)") When i run it it says it can't find the...
1
by: 2D Rick | last post by:
I want to open a report using OpenReport and passing it a Where Clause with multiple criteria. I know the Where Clause below is way off but it conveys what I need. Dates are of string type. ...
0
by: ChadK | last post by:
I am trying to open a report based on what the user selects on a form. Each individual criteria works but when I try to combine to pass multiple criteria it doesn't. I have read what I can find on...
2
by: Emre DÝNÇER | last post by:
is it possible to have a multiple criteria switch in C# switch(name , surname){ case "John","Smith" break; } thanks in advance
1
by: akirekab | last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria. Here is sample of what i need. =DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
2
by: trinismooth | last post by:
Hi All Trying to do a dcount with multiple criteria for example: =DCount("","access rights"," = 'kev'" and ='Christian Fellowship members temp'" And ='yes'") Thanks for any assistance
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.