How to get "from this year on"

  • Thread starter Thread starter Wind54Surfer
  • Start date Start date
W

Wind54Surfer

Hi all,

I am a newbie trying to retrieve all records from say "1998" on(to the
latest).

Presently I have :

Like "*" & "/" & "*" & "/" & [Enter Year:]

I know I need to use ">=" and tried many combinations unsuccessfully.

Can someone please help me.
Thanks in advance,
Emilio
 
It all starts with the data...

?!What data?!

Do you have a field that holds dates? Does Access think that field holds
dates (i.e., a Date/Time data type)? Or is that field defined as a "text"
field? The text string "1/8/2008" is NOT a date, at least not to Access.

In a query, add a new field, something like:

NewField: Year([YourDateTimeField])

in the Selection Criterion under that new field, add something like:

[Enter the year]

Now add any other fields you'll want to see in your query output.

When you run this query, Access "calculates" the year of the datetime field,
then prompts for a parameter against which to compare the calculated year.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I am sorry, I "assumed" again

--------------------------------------------------------------------------------------------------------------
SELECT DISTINCTROW Orders.OrderID, CustNew.Initial, CustNew.CustomerName,
CustNew.Address, CustNew.[Home#], CustNew.PostalCode, Orders.OrderDate,
[Schedule Info].Lead, [Schedule Info].Referral, Items.Items, Items.[#],
Items.Color
FROM ((CustNew INNER JOIN Orders ON CustNew.CustomerID = Orders.CustomerID)
INNER JOIN (JobSchedule INNER JOIN [Schedule Info] ON JobSchedule.Scheduled =
[Schedule Info].JobCode) ON Orders.OrderID = [Schedule Info].OrderID) LEFT
JOIN Items ON [Schedule Info].OrderID = Items.OrderID
WHERE (((CustNew.PostalCode) Like [Enter Postal Code:] & "*") AND
((Orders.OrderDate) Like "*" & "/" & "*" & "/" & [Enter Year:]) AND
(([Schedule Info].Referral)="Yes") AND ((JobSchedule.Status)="Done") AND
((Orders.Condominium)=No))
ORDER BY Orders.OrderDate DESC;
--------------------------------------------------------------------------------------------------------------------
Please let me know if need anything else,
Thanks for your help


Jeff Boyce said:
It all starts with the data...

?!What data?!

Do you have a field that holds dates? Does Access think that field holds
dates (i.e., a Date/Time data type)? Or is that field defined as a "text"
field? The text string "1/8/2008" is NOT a date, at least not to Access.

In a query, add a new field, something like:

NewField: Year([YourDateTimeField])

in the Selection Criterion under that new field, add something like:

[Enter the year]

Now add any other fields you'll want to see in your query output.

When you run this query, Access "calculates" the year of the datetime field,
then prompts for a parameter against which to compare the calculated year.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Wind54Surfer said:
Hi all,

I am a newbie trying to retrieve all records from say "1998" on(to the
latest).

Presently I have :

Like "*" & "/" & "*" & "/" & [Enter Year:]

I know I need to use ">=" and tried many combinations unsuccessfully.

Can someone please help me.
Thanks in advance,
Emilio
Click to expand...
Click to expand...
 
You sent a copy of your query.

I have no way to know if your query includes all the fields from your
table(s).

What happens when you use the approach I suggested to limit the year?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Wind54Surfer said:
I am sorry, I "assumed" again

--------------------------------------------------------------------------------------------------------------
SELECT DISTINCTROW Orders.OrderID, CustNew.Initial, CustNew.CustomerName,
CustNew.Address, CustNew.[Home#], CustNew.PostalCode, Orders.OrderDate,
[Schedule Info].Lead, [Schedule Info].Referral, Items.Items, Items.[#],
Items.Color
FROM ((CustNew INNER JOIN Orders ON CustNew.CustomerID =
Orders.CustomerID)
INNER JOIN (JobSchedule INNER JOIN [Schedule Info] ON
JobSchedule.Scheduled =
[Schedule Info].JobCode) ON Orders.OrderID = [Schedule Info].OrderID) LEFT
JOIN Items ON [Schedule Info].OrderID = Items.OrderID
WHERE (((CustNew.PostalCode) Like [Enter Postal Code:] & "*") AND
((Orders.OrderDate) Like "*" & "/" & "*" & "/" & [Enter Year:]) AND
(([Schedule Info].Referral)="Yes") AND ((JobSchedule.Status)="Done") AND
((Orders.Condominium)=No))
ORDER BY Orders.OrderDate DESC;
--------------------------------------------------------------------------------------------------------------------
Please let me know if need anything else,
Thanks for your help


Jeff Boyce said:
It all starts with the data...

?!What data?!

Do you have a field that holds dates? Does Access think that field holds
dates (i.e., a Date/Time data type)? Or is that field defined as a
"text"
field? The text string "1/8/2008" is NOT a date, at least not to Access.

In a query, add a new field, something like:

NewField: Year([YourDateTimeField])

in the Selection Criterion under that new field, add something like:

[Enter the year]

Now add any other fields you'll want to see in your query output.

When you run this query, Access "calculates" the year of the datetime
field,
then prompts for a parameter against which to compare the calculated
year.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Wind54Surfer said:
Hi all,

I am a newbie trying to retrieve all records from say "1998" on(to the
latest).

Presently I have :

Like "*" & "/" & "*" & "/" & [Enter Year:]

I know I need to use ">=" and tried many combinations unsuccessfully.

Can someone please help me.
Thanks in advance,
Emilio
Click to expand...
Click to expand...
Click to expand...
 
Thank You Mr. Vinson, that was the answer!

Thank you all for your quick response.



John W. Vinson said:
Hi all,

I am a newbie trying to retrieve all records from say "1998" on(to the
latest).
Click to expand...

A date/time field IS NOT A STRING.

Try a criterion on the date field of
= DateSerial([Enter year:], 1, 1)
Click to expand...


John W. Vinson [MVP]
Click to expand...
 
Back
Top