A 2010 like date query

[Dick7Access]

I have a underlining year query with a “like” criteria so user can pick own year for report. Unfortunately we need only the year be entered as we want all months showing for desired year. The data that is being queried is in (mm/dd/yyyy) format typing( yyyy) of course brings up a blank. Where do I go from here? Someone please point me in the right direction.

 

[John Big Booty]

Use the Year() function to extract the Year value from your dates and then use your Year criteria against that expression.

 

[Dick7Access]

I shall try it Thanks

 

[spikepl]

Actually, Like "*/*/yyyy", where yyyy is the year you want, or Like "mm/*/yyyy", with mm being the month, also works as criterion in a date field

 

[Dick7Access]

Actually, Like "*/*/yyyy", where yyyy is the year you want, or Like "mm/*/yyyy", with mm being the month, also works as criterion in a date field

I just had a chance to look over you post and realized I dd not present my question properly. It not just the like operator that I need but the

 like[Enter Year]&"*"

That I need so that the user can pick his own year. The above query will not return the value I need unless the user enters month day and year. I need to be able to pull up records with the desired year regardless of the month or day associated with it. I am still working with Big John's post but haven't had time to fully study it. Thanks Again!

 

[Dick7Access]

Use the Year() function to extract the Year value from your dates and then use your Year criteria against that expression.

Works great, Thanks again John

 Year: Right([txtStartDate],4)             txtNameF  txtNameL
                                                              tblMain     tblMain
  Criteria: Like [Enter Year] & "*"

 

[Pat Hartman]

The data that is being queried is in (mm/dd/yyyy) format

Dates are not stored as strings, they are stored as double precision numbers that's why using Like doesn't work. Use the appropriate date function as John suggested.

 

[Dick7Access]

Dates are not stored as strings, they are stored as double precision numbers that's why using Like doesn't work. Use the appropriate date function as John suggested.

Yes, see the post my post just before yours!

 

[Dick7Access]

Use the Year() function to extract the Year value from your dates and then use your Year criteria against that expression.

John, this query works good, by its self but when I open a report base on that query it ask for a year, I enter a year, but then it ask for a start date without entering a date I can click ok and it give me the correct data.

  Year: Right([txtStartDate],4)             txtNameF  txtNameL
                                                         tblMain     tblMain
  Criteria: Like [Enter Year] & "*"

 

[John Big Booty]

Dick I'm not sure how you are doing this but I would have a form, with a text box (or better still a combo showing only the available years) to enter the year. Your query would be based on this form. You form would then have a Button that opens the Report that uses your query as it's Record Source.

 

[John Big Booty]

Here's a very quick sample

 

[Dick7Access]

Dick I'm not sure how you are doing this but I would have a form, with a text box (or better still a combo showing only the available years) to enter the year. Your query would be based on this form. You form would then have a Button that opens the Report that uses your query as it's Record Source.

To start with at this stage its just experimental. I am open to trying whatever is the best code development. My client is pleased with V 1.2 and he is looking for bugs and to see if it is covering every thing he needs. This gives me time to work on V 1.3 which will have reports. So I will see if I can go in the direction of combo box like you suggested with the years as I did with the states. Thanks again you are always helpful. You will be even much helpful when I can get some of those giant grayfish. Two nights ago I had snow crab legs all you can eat. I wimped out at four clusters.
PS. If would like to look at V 1.2 and give me some advice let me know and I will send you the past word.

 

[Dick7Access]

Here's a very quick sample

Works well but I am confused?? Why is the report name put into a variable?
[FONT=&quot]How does [/FONT]

 [FONT=&quot][forms]![FRM_Welcome]![combo0][/FONT]

[FONT=&quot]pick up with is selected in Combo0?

[/FONT] I understand this MsgBox "Please select a Year from the list" I believe it is call user proof!!

[FONT=&quot]But Why is this necessary [/FONT]

Me.Combo0.Dropdown

I could just use your code but I will never learn doing it that way. I need to know what is happening. Also what does (Expr1) mean? I have seen it in other code snippets.

 

[John Big Booty]

The Report name is put in as a variable simply because that's the way the wizard does it in Access '03.

[Forms]![YourCurrentFormName]![AnyControlName]

Is the syntax for picking up the value held in a control on a form relevant to the current record.

Me.Combo0.Dropdown

This simply drops down the list, so is really just a flourish and not really necessary, it will however highlight to even the demist user what is required of them

 

[John Big Booty]

Expr1 is simply the default label that is given by Access to a derived field in a query, and could be replaced by almost anything that didn't conflict with any other field name or reserved word.

 

[John Big Booty]

Have a look at the amended version of the Sample to see what I mean

 

[Dick7Access]

Expr1 is simply the default label that is given by Access to a derived field in a query, and could be replaced by almost anything that didn't conflict with any other field name or reserved word.

Thanks John I got it, at least 98%. I will try building my own. I leave in the morning for TX so it probable be a while before I get back to serious developing.

 

[Pat Hartman]

Yes, see the post my post just before yours!

I guess I shouldn't leave the window open so long as I am answering a question

 

[John Big Booty]

Thanks John I got it, at least 98%. I will try building my own. I leave in the morning for TX so it probable be a while before I get back to serious developing.

I hope they've got some good seafood for you

 

[Dick7Access]

I hope they've got some good seafood for you

My frst stop is Mobile, AL. My sister in law is taking me to a famous sea food resturant out on the causeway. She usually takes us to a Place called "The Original Oyster House" that is fabulous, but she said she found one even better. My next stop is Slidell, LA, and that church takes me to a place called Copeland,and they have the best bar-a-que shrimp. Next, Hello Texas and Red Lobster. Do you get the impression I travel to eat.