How to get "from this year on"

[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

 

[Jeff Boyce]

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]

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

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

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

 

[Jeff Boyce]

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

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

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

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

 

[John W. Vinson]

Hi all,

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

A date/time field IS NOT A STRING.

Try a criterion on the date field of

= DateSerial([Enter year:], 1, 1)

John W. Vinson [MVP]

 

[Wind54Surfer]

Thank You Mr. Vinson, that was the answer!

Thank you all for your quick response.

Hi all,

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

A date/time field IS NOT A STRING.

Try a criterion on the date field of

= DateSerial([Enter year:], 1, 1)

John W. Vinson [MVP]