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
> Query's With Date-type Criteria, Access 2016    
 
   
Sami
post Jun 15 2019, 03:37 AM
Post#1



Posts: 42
Joined: 3-April 19



Hello every one
I made calculated column in a query with date criteria, it was
CODE
DCount("[ID2]";"[Table2 Query]";"[Date2]=#"&[Date2]&"#")

but it gave zero.
while I use certain value it gave correct result
CODE
DCount("[ID2]";"[Table2 Query]";"[Date2]=#13/06/2019#")

any suggestion to make the query work correctly.
thanks
This post has been edited by Sami: Jun 15 2019, 03:44 AM
Go to the top of the page
 
Phil_cattivocara...
post Jun 15 2019, 04:08 AM
Post#2



Posts: 304
Joined: 2-April 18



Could you post full SQL, please?What is your "regionals"? default date format: MM/DD/YYYY, DD/MM/YYYY or what?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
cheekybuddha
post Jun 15 2019, 04:13 AM
Post#3


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Hi Sami,

What is your regional date format? If it is not mm/dd/yyyy, then you must make sure you format your dates specially to pass to SQL (or in this case DCount()).

CODE
DCount("[ID2]";"[Table2 Query]";"[Date2]=" & Format([Date2], "\#yyyy\-mm\-dd hh:nn:ss\#"))

(ISO date format)
or
CODE
DCount("[D2]";"[Table2 Query]";"[Date2]=" & Format([Date2], "\#mm/mm/dd hh:nn:ss\#"))

(US date format)

I used the format function to add the hash delimiters at the same time.

This doesn't affect your dates and how they are displayed in your tables/forms.

This is because SQL requires dates to be passed in an unambiguous date format (ie, one of the two formats suggested above).

For me in the UK, 01/06/2019 means 1st June 2019, but in the US it means 6th January 2019.

SQL doesn't know that I mean UK date format and just assumes it should be 6th January 2019.

For your example of 13/06/2019, it should work correctly anyway.


Ah, I didn't read properly!!

You are doing this in a query.

So, don't use DCount(), use an aggregate query with GROUP BY.

It would be helpful to see the rest of your query SQL, but you need something like:
CODE
SELECT
  Date2,
  COUNT(Date2) AS Occurrences
FROM [Table2 Query]
GROUP BY Date2;


hth,

d


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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 15 2019, 04:31 AM
Post#4



Posts: 42
Joined: 3-April 19



thanks Phil_cattivocara...
I set my computer date format in dd/mm/yyyy
CODE
SELECT Table2.ID2, Table2.Positions, Table2.Linker, Table2.Date2, DCount("[ID2]","[Table2 Query]","[ID2]<" & [ID2] & "and [Linker]=" & [Linker]) AS Expr1, DCount("[ID2]","[Table2 Query]","[Date2] = # " & [Date2] & " # ") AS Expr2
FROM Table2;

This post has been edited by Sami: Jun 15 2019, 04:36 AM
Go to the top of the page
 
Phil_cattivocara...
post Jun 15 2019, 04:47 AM
Post#5



Posts: 304
Joined: 2-April 18



Read cheekybuddha post too. It is better not to use DCount very often in queries.
You have 2 calculated fields. Are you sure the problem is in the second DCount?
What kind of field is Date2? Number, Text, Date/Time? And ID2 and Linker? Text, Number... what?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Sami
post Jun 15 2019, 05:03 AM
Post#6



Posts: 42
Joined: 3-April 19



Thanks
Date2 is time/date field
And I need to make auto number in the query which had bettwen 5-11 records. The criteria are the date2 field (time/date) , linker field (number) and id2 field (number)
This post has been edited by Sami: Jun 15 2019, 05:05 AM
Go to the top of the page
 
Phil_cattivocara...
post Jun 15 2019, 05:45 AM
Post#7



Posts: 304
Joined: 2-April 18



1) try with one criteria every time, not both, and see if you get the right result, separataly;
2) try to modify SQL like this (verify spaces characters too inside double quotes)
CODE
SELECT Table2.ID2, Table2.Positions,   Table2.Linker, Table2.Date2, DCount("[ID2]","[Table2 Query]","[ID2]< "   & [ID2] & " and [Linker]= " & [Linker]) AS Expr1,   DCount("[ID2]","[Table2 Query]","[Date2] = #" & [Table2].[Date2] & "#") AS Expr2
     FROM Table2;

3) change your way and follow what cheekybuddha suggested.
4) post a little sample of your database.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
cheekybuddha
post Jun 15 2019, 07:45 AM
Post#8


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Hi Sami,

What is the SQL of [Table2 Query] ?

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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 15 2019, 08:48 AM
Post#9



Posts: 42
Joined: 3-April 19



Hi cheekybuddha
Thanks a lot I have been trying to make your code meet my needs since I read your comment before I replay yours.
But I couldn't get that.
I want to make column of autonumber for the query.
There is there criteria in this column.
One of them didn't work and I get it speratly in SQL statement to make my question specific as possible.
CODE
SELECT Table2.ID2, Table2.Positions, Table2.Linker, Table2.Date2, DCount("[ID2]","[Table2 Query]","[ID2]<" & [ID2] & "and [Linker]=" & [Linker]) AS Expr1, DCount("[ID2]","[Table2 Query]","[Date2] = # " & [Date2] & " # ") AS Expr2
FROM Table2;

The original code was with one calculated column and I made two columns to specify where the problem is.
This post has been edited by Sami: Jun 15 2019, 08:55 AM
Go to the top of the page
 
cheekybuddha
post Jun 15 2019, 09:08 AM
Post#10


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


In your DCount() expressions you reference [Table2 Query].

Is this a separate query? What is its SQL?

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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 15 2019, 12:46 PM
Post#11



Posts: 42
Joined: 3-April 19



Thanks
That is the same query
Go to the top of the page
 
cheekybuddha
post Jun 16 2019, 06:28 AM
Post#12


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Hi Sami,

I don't think you will be able to use DCount() if you are trying to refer to the actual same query where they are being evaluated. Perhaps you can, but I can't see how.

I'm still not 100% sure what you are trying to see, but my guess is that are trying to see both the count of each occurrence of Date2 per ID2, and also a ranking of Linker per ID2.

Is this correct?

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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 16 2019, 01:00 PM
Post#13



Posts: 42
Joined: 3-April 19



Thank you very much
Sir.
You gave me the solution
Although the solution was struck out
CODE
DCount("[ID2]";"[Table2 Query]";"[Date2]=" & Format([Date2], "\#yyyy\-mm\-dd hh:nn:ss\#"))
Go to the top of the page
 
cheekybuddha
post Jun 16 2019, 03:15 PM
Post#14


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


OK, I'm glad it works! thumbup.gif

What language version of Access are you using?

';' is used for parameter separators in non-English versions of Access.

d

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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 16 2019, 03:32 PM
Post#15



Posts: 42
Joined: 3-April 19



I set widows language as Arabic.
and i use office 2010 English and 2016 Arabic I get the problem in both of them.
thank you for your help.
This post has been edited by Sami: Jun 16 2019, 04:09 PM
Go to the top of the page
 
cheekybuddha
post Jun 16 2019, 03:33 PM
Post#16


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Just to be sure, it is working for you now?

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


Regards,

David Marten
Go to the top of the page
 
Sami
post Jun 16 2019, 04:12 PM
Post#17



Posts: 42
Joined: 3-April 19



I altered the code to work
CODE
DCount("[ID]";"[Table2 Query]";"[Date2]=#" & Format([Date2];"yyyy/mm/dd") & "#")

This post has been edited by Sami: Jun 16 2019, 04:14 PM
Go to the top of the page
 
cheekybuddha
post Jun 17 2019, 03:07 AM
Post#18


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

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


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 06:42 AM