query based on query (1 Viewer)

[zezo2021]

First Query
SELECT Year([MyDate]) AS YearfromDate
FROM TransactionTable;

Second Query
SELECT Query1.YearfromDate
FROM Query1
GROUP BY Query1.YearfromDate;



Why does this error appear

Invalid procedure call
When run query 2


and what is the solution

Thank you so much

 

[tvanstiphout]

What happens when you run Query1?
Is your database in a Trusted Location?
What happens if you use Ctrl+G to open the immediate window, and enter:
?Year(Date)
2024

 

[June7]

I do not get error.

Why do you have 2 queries when 1 would produce the same output?

 

[zezo2021]

What happens when you run Query1?
Is your database in a Trusted Location?
What happens if you use Ctrl+G to open the immediate window, and enter:
?Year(Date)
2024

The trusted location is ok
Macro enabled
The immediate window is work

 

[zezo2021]

I do not get error.

Why do you have 2 queries when 1 would produce the same output?

When adding a groupby to query1 same error appears

 

[CJ_London]

and if you run query1 on it's own - does that work an list all years? and are all the returned values valid i.e. no chinese type characters

also try this query

SELECT DISTINCT Year([MyDate]) AS YearfromDate
FROM TransactionTable;

 

[ebs17]

Invalid procedure is not the query, but the function year.

@tvanstiphout gave reasons why this simple function might not work here.
If you have to question everything, I would also question whether MyDate is a Date data type field or something else, since Year e requires a date value as an argument.

 

[zezo2021]

Invalid procedure is not the query, but the function year.

@tvanstiphout gave reasons why this simple function might not work here.
If you have to question everything, I would also question whether MyDate is a Date data type field or something else, since Year e requires a date value as an argument.

I investigated the date field and found an unexpected value. It might be the cause, because when I created a new table with correct dates, the Year function worked. This incorrect value is the result of incorrect input for many rows, such as:

Empty values
Only hours and minutes

Thank you all for your help

 

[zezo2021]

Here is the DB
the error still there after removing blanks and hours and minutes and checking the date using VBA CODE

 

[CJ_London]

you still have zeros in some of your records
TransactionTable

ID	amount	MyDate
392​	10.00​	00:00:00​
390​	178.00​	00:00:00​
389​	90.00​	00:00:00​
388​	108.00​	00:00:00​
394​	50.00​	00:00:00​
393​	50.00​	00:00:00​
391​	20.00​	00:00:00​

except they are not zeros, they have a value of 6684676, it is just the format which prevents using a date with a year greater than 9999

write this query
SELECT TransactionTable.*, CDbl([MyDate]) AS Expr1
FROM TransactionTable;

and you will get this result
Query1 Query1

ID	amount	MyDate	Expr1
392​	10.00​	00:00:00​	6684676​
390​	178.00​	00:00:00​	6684676​
389​	90.00​	00:00:00​	6684676​
388​	108.00​	00:00:00​	6684676​
394​	50.00​	00:00:00​	6684676​
393​	50.00​	00:00:00​	6684676​
391​	20.00​	00:00:00​	6684676​
441​	70.00​	02/01/2024​	45293​

the last record is the first with a valid date value

Remove these records and the aggregate query runs

Recommend your review your import processes to prevent this happening

edit: FYI 6684676 represents the year 22595

 

[Pat Hartman]

This incorrect value is the result of incorrect input for many rows, such as:

Empty values
Only hours and minutes

That is why we always add validation code to our form's BeforeUpdate event. It keeps bad data from being saved.

You can't always prevent irrational data but with dates, you can at least use sanity checks. Certain types of dates such as birth and death dates cannot be in the future. Other types of dates should be within some reasonable period of time. For example 1/2/202 is a valid date but it isn't rational for most applications. It is almost certainly a typo. So making sure that dates fall into a logical range traps that type of error. Bad data is bad for business and it is your job to ensure that it doesn't infiltrate your database.

 

[zezo2021]

you still have zeros in some of your records
TransactionTable

ID	amount	MyDate
392​	10.00​	00:00:00​
390​	178.00​	00:00:00​
389​	90.00​	00:00:00​
388​	108.00​	00:00:00​
394​	50.00​	00:00:00​
393​	50.00​	00:00:00​
391​	20.00​	00:00:00​

except they are not zeros, they have a value of 6684676, it is just the format which prevents using a date with a year greater than 9999

write this query
SELECT TransactionTable.*, CDbl([MyDate]) AS Expr1
FROM TransactionTable;

and you will get this result
Query1 Query1

ID	amount	MyDate	Expr1
392​	10.00​	00:00:00​	6684676​
390​	178.00​	00:00:00​	6684676​
389​	90.00​	00:00:00​	6684676​
388​	108.00​	00:00:00​	6684676​
394​	50.00​	00:00:00​	6684676​
393​	50.00​	00:00:00​	6684676​
391​	20.00​	00:00:00​	6684676​
441​	70.00​	02/01/2024​	45293​

the last record is the first with a valid date value

Remove these records and the aggregate query runs

Recommend your review your import processes to prevent this happening

edit: FYI 6684676 represents the year 22595

 

[zezo2021]

you still have zeros in some of your records
TransactionTable

ID	amount	MyDate
392​	10.00​	00:00:00​
390​	178.00​	00:00:00​
389​	90.00​	00:00:00​
388​	108.00​	00:00:00​
394​	50.00​	00:00:00​
393​	50.00​	00:00:00​
391​	20.00​	00:00:00​

except they are not zeros, they have a value of 6684676, it is just the format which prevents using a date with a year greater than 9999

write this query
SELECT TransactionTable.*, CDbl([MyDate]) AS Expr1
FROM TransactionTable;

and you will get this result
Query1 Query1

ID	amount	MyDate	Expr1
392​	10.00​	00:00:00​	6684676​
390​	178.00​	00:00:00​	6684676​
389​	90.00​	00:00:00​	6684676​
388​	108.00​	00:00:00​	6684676​
394​	50.00​	00:00:00​	6684676​
393​	50.00​	00:00:00​	6684676​
391​	20.00​	00:00:00​	6684676​
441​	70.00​	02/01/2024​	45293​

the last record is the first with a valid date value

Remove these records and the aggregate query runs

Recommend your review your import processes to prevent this happening

edit: FYI 6684676 represents the year 22595

Please explain to me if it is possible if I want to check a date after converting it to Double format
between Min(Double) AND Max (Double)
please mention
What is Min smallest date in Double format
What is the largest date in Double format

so I can extract the incorrect dates if I use this condition in the query

Thank you for your understanding

 

[CJ_London]

Dates are stored as decimal numbers - the bit before the decimal point is the number of days since 30/12/1899, so 31/12/1899 is 1, 1/1/1900 is 2, etc.

if the date is 29/12/1899 the the number would be -1.

the bit after the decimal point is the time expressed as the number of seconds divided by the total number of seconds in a day (86400) so 12 noon is 0.5, 6pm 0.75 etc

so the minimum date can be anything you like,as can the maximum date. You have to decide. If your transactions cannot be before say 1/1/2018 that would be your minimum and if it cant be later than today, that is a moving target, use the date function

E.g Mydate<cdbl(date())

 

[Pat Hartman]

Why would you be converting a date to double this way? A datetime field IS behind the scenes a double. By expressly converting it to a double this way, you are forcing your parameter to also be a double.

Using Min() and Max() this way assumes the data is coming from the current recordset and so Between Min and Max makes no sense.

 

[CJ_London]

Why would you be converting a date to double this way?

because the OP has some weird large numbers - see post 10. Viewing as a double makes it easier to see

Using Min() and Max() this way assumes the data is coming from the current recordset

My reading is the OP wants to set a min and max date/number range as a validation rule

 

[Pat Hartman]

Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Access stores dates before December 30, 1899 as negative numbers.

Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The numeric value represents a fraction of one day.

Store, calculate, and compare Date/Time data - Microsoft 365 Apps

Provides information on how to store, calculate, and compare Date/Time data in Access and discusses why you may get unexpected results when you calculate dates and times or compare dates and times.

learn.microsoft.com

This is the limit of the value for a date data type. It tops out at 2,958,465 so the value 6,684,676 is invalid.

 

[zezo2021]

Thanks for help

My goal is to extract incorrectly entered dates. I found dates with a value of 0:0:0. I couldn't filter them out, so I couldn't extract them to correct them. I suggested that I convert them to numbers, which would allow me to quickly find the errors.

 

[GPGeorge]

Thanks for help

My goal is to extract incorrectly entered dates. I found dates with a value of 0:0:0. I couldn't filter them out, so I couldn't extract them to correct them. I suggested that I convert them to numbers, which would allow me to quickly find the errors.

Can you sort the dates, both in ascending and descending order? And apply a filter like YourDateFieldNameGoesHere < #2000/1/1# OR YourDateFieldNameGoesHere >=Date() to search for both impossibly old dates and probably invalid future dates?

 

[CJ_London]

The problem is you can only compare dates - the OP's field contains the number 6684676. In a comparison, you get this error

use the clng function and you get a valid response

have to say, I don't understand how these values got into the field in the first place. I assume thy have been imported somehow.