Date Criteria

[Guest]

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

Sadie

 

[Guest]

In the query create another field using the function Year to return the Year

Year([DateFieldName])

In the criteria under the new field you can write
[Please select a year]

The user will be prompt to enter a year (2007)

====================
In SQL it will look like

Select * From TableName Where Year([DateFieldName]) = [Please select a year]
====================

Note: that will work if the field data type is Date, if it's a text field
try using the function right to get only the year

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

 

[Guest]

Thank you for that, however I'm being particularly thick today and can't get
it to work. Do I create another field in the table or in the query? By the
way, the Date field is a text field.

In the query create another field using the function Year to return the Year

Year([DateFieldName])

In the criteria under the new field you can write
[Please select a year]

The user will be prompt to enter a year (2007)

====================
In SQL it will look like

Select * From TableName Where Year([DateFieldName]) = [Please select a year]
====================

Note: that will work if the field data type is Date, if it's a text field
try using the function right to get only the year

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

--
Good Luck
BS"D

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

Sadie

 

[Guest]

You can copy this SQL

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

Change the TableName and DateFieldName to your objects names, and paste it
to a query and then run it.

If you need help with the SQL, then post the table name and date field name

--
Good Luck
BS"D

Thank you for that, however I'm being particularly thick today and can't get
it to work. Do I create another field in the table or in the query? By the
way, the Date field is a text field.

In the query create another field using the function Year to return the Year

Year([DateFieldName])

In the criteria under the new field you can write
[Please select a year]

The user will be prompt to enter a year (2007)

====================
In SQL it will look like

Select * From TableName Where Year([DateFieldName]) = [Please select a year]
====================

Note: that will work if the field data type is Date, if it's a text field
try using the function right to get only the year

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

--
Good Luck
BS"D

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

Sadie

 

[Guest]

Thanks Ofer, after a walk in the fresh air and your reply I finally got it to
work.

You can copy this SQL

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

Change the TableName and DateFieldName to your objects names, and paste it
to a query and then run it.

If you need help with the SQL, then post the table name and date field name

--
Good Luck
BS"D

Thank you for that, however I'm being particularly thick today and can't get
it to work. Do I create another field in the table or in the query? By the
way, the Date field is a text field.

In the query create another field using the function Year to return the Year

Year([DateFieldName])

In the criteria under the new field you can write
[Please select a year]

The user will be prompt to enter a year (2007)

====================
In SQL it will look like

Select * From TableName Where Year([DateFieldName]) = [Please select a year]
====================

Note: that will work if the field data type is Date, if it's a text field
try using the function right to get only the year

Select * From TableName Where Right([DateFieldName],4) = [Please select a
year]

--
Good Luck
BS"D


:

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

Sadie

 

[John W. Vinson]

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

So you have *several dates* in the same field? Ofer's second suggestion will
work only for the *last* date. You're going to have trouble because (as is
often the case for Works databases) your data is not properly normalized!

If you have data like

04.14.2004 05.20.2001 03.18.2006

then it becomes very hard to dig through the thicket and find just the year.
If you have

04.14.04 05.20.01 03.18.06

it's downright impossible, because you can't tell the years apart from the
months and days!

Try

LIKE "*" & [Enter four digit year:] & "*"

This will find (say) 2001 no matter where in the string it occurs.

John W. Vinson [MVP]

 

[Guest]

Thank you John. Yes that does work much better. Can I do similar but with a
variable number input from user?

Imported data from Works database, dates are in format dd.mm.yyyy. Several
dates in same record of field. I want user to be able to enter parameter of
the year only when running a query. What criteria should I use, and what
should the input be from the user?

So you have *several dates* in the same field? Ofer's second suggestion will
work only for the *last* date. You're going to have trouble because (as is
often the case for Works databases) your data is not properly normalized!

If you have data like

04.14.2004 05.20.2001 03.18.2006

then it becomes very hard to dig through the thicket and find just the year.
If you have

04.14.04 05.20.01 03.18.06

it's downright impossible, because you can't tell the years apart from the
months and days!

Try

LIKE "*" & [Enter four digit year:] & "*"

This will find (say) 2001 no matter where in the string it occurs.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you John. Yes that does work much better. Can I do similar but with a
variable number input from user?

Ummmm...

That DOES prompt for variable number input from the user.

Could you post the actual SQL string of the query that you entered, and what
result you're getting?

John W. Vinson [MVP]

 

[Guest]

Sorry, not making myself clear. Your solution worked well on the date field.
Should have said a variable number of letters or numbers for user to input
for a different field that I need to design another select query.

 

[John W. Vinson]

Sorry, not making myself clear. Your solution worked well on the date field.
Should have said a variable number of letters or numbers for user to input
for a different field that I need to design another select query.

Well, I've got a longstanding policy that I don't answer questions that I have
not been asked! <g>

If you can explain the nature of this other query, I'm sure someone can help.

John W. Vinson [MVP]