• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Updating a Query to 'add or subtract' a year for different results

I'm trying to create a button in a form that can change a query. I have to change the year that it gets the information from (to find out how much was made for a certain year- whether we need to go back in time or forward in time from where the query starts). I'd most likely need to be able to tell the user of the DB what year the query was currently on, also... so that they know whether or not to add/subtract a year to get information from the year they need. So that would have to be updated every time one of the buttons changed the year in the query.

Here is the SQL for my query:

[quote]
SELECT projects.[Project Name], projects.[Client Name], projects.[Service Type], projects.[Date Completed], projects.[Final Payment]
FROM projects
WHERE (((projects.[Date Completed]) Between #1/1/2004# And #12/31/2004#));
[/quote]

As you can see, I need possibly a Macro button that could change the year from 2004 to 2005 when I want to add a year (and add a year every time the button was clicked), and another Macro button that could change the year from 2004 to 2003 (and subtract a year every time the button was clicked).

I'm stressed out because this has to be done in Access and I have to have it done by tonight, so anyone who helps- it is much appreciated. You'd be a life saver.

By the way- I'm using Access 2003
0
mrE
Asked:
mrE
  • 18
  • 10
  • 3
1 Solution
 
dannywarehamCommented:
How about having a combo box with a list of year values.
Then use this value in your query (something like):

SELECT projects.[Project Name], projects.[Client Name], projects.[Service Type], Format(projects.[Date Completed],"dd mmm") as OneDate, Format(projects.[Date Completed],"yyyy") as TwoDate, projects.[Final Payment]
FROM projects
WHERE (((Format(projects.[Date Completed],"dd mmm")) Between "01 Jan" AND "31 Dec") AND (Format(projects.[Date Completed],"yyyy") = Me.Combo1));
0
 
mrEAuthor Commented:
It sounds like your solution would work but I'm not exactly sure what a combo box is... or how your solution would work.
Like- how do I incorporate my buttons to change the year? and how do I display what year it is in the form?
0
 
dannywarehamCommented:
OK. A combo is a dropdown list - you get them on forms.

Create one with the wizard as a "value List" - the wizard will point this out.
then enter your values (2000, 2001, 2002 etc)

What's i've done in your query is to split your date.
It will now return teh DD MMM part in one column and teh YYYY part in another
What we then do is always return 1 Jan to 31 Dec, but let the user specify the Year part.
They do this through the value in the combo box.

Does that sound OK?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mrEAuthor Commented:
I'm slowly starting to understand how to do this.
Do I have to change any existing tables or add any new tables? Woulnd't I need to add a table with the year values and then add that drop down list? How does the query get the year from the drop down list? Are there any 'variables' I have to change in your SQL statement to direct it to the right form and drop down list?
0
 
mrEAuthor Commented:
I just noticed how to make the combo box. I'm sorry. I should have looked for it before I asked you about it.
So what I've got now is a combo box that is looking up values in another table I called "Years".
So does it automatically change the query?
0
 
dannywarehamCommented:
>>Woulnd't I need to add a table with the year values and then add that drop down list?
No. You can do it with VALUE LIST - where you manually type the values.
You can link to a table if you want, but there's no need.

Your query will need altering.
When yuo've created your combo, and got teh values into it, go into properties, ALL tab and NAME and call it:
cboYear_List

Now you can reference this in your query:

SELECT projects.[Project Name], projects.[Client Name], projects.[Service Type], Format(projects.[Date Completed],"dd mmm") as OneDate, Format(projects.[Date Completed],"yyyy") as TwoDate, projects.[Final Payment]
FROM projects
WHERE (((Format(projects.[Date Completed],"dd mmm")) Between "01 Jan" AND "31 Dec") AND (Format(projects.[Date Completed],"yyyy") = Me.cboYear_List));
0
 
mrEAuthor Commented:
Right now I've got a form called  "queries/reports" that has the combo box. I changed the SQL code for the query named "income by year" to the SQL that you gave me. I changed the name of my drop down list to "Me.Combo1".
I added a command button to run the "income by year" query. It pops up an input box that says "Me.Combo1" and I have to put the year in there to make it work.
0
 
mrEAuthor Commented:
I do have a need to link it to a table because in the future we'd need to be able to add more years- depending on how many years the business is together. The people using this form aren't smart enough to go into design view to add more values manually to the combo box
0
 
mrEAuthor Commented:
and now I've named the combo box to "Combo1".
0
 
dannywarehamCommented:
OK.
The reason that it asks for "Me.Combo1" is because it can't find the control.
You need to change this to teh name of teh one on your form ie. cboYear_List.
If you're diong this in an external query, you will need to reference the form as well as the control:

[Forms]![queries/reports]![cboYear_List]


>>I do have a need to link it to a table because in the future we'd need to be able to add more years- depending on how many years the business is together
Fair enough - you've obviously planned ahead..!
:-)
0
 
mrEAuthor Commented:
Ok. Two problems.

It works but it just gives back one record with "0" values where "client name" and "service type" are. Those are drop-down values so that's why it's "0". But anyway it needs to bring up the records lol.

I'd also like to change the formatting of how the dates come back. Can I change "OneDate" to "Day/Month" and have it show the month first, and the day of the month next (rather than "dd mmm") and change "TwoDate" to "Year"? I know how to change the names of the columns in the SQL- but will it mess it up? and how do I format it differently?
0
 
dannywarehamCommented:
>.It works but it just gives back one record with "0" values where "client name" and "service type" are. Those are drop-down values so that's why it's "0"

This is because of how the query is written.
Post your SQL and I'll fiddle.

>>I'd also like to change the formatting of how the dates come back. Can I change "OneDate" to "Day/Month" and have it show the month first, and the day of the month next (rather than "dd mmm") and change "TwoDate" to "Year"?

Again, I'll show you in the SQL - it's all in the FORMAT(blah blah, "mm dd") bit

:-)
0
 
mrEAuthor Commented:
the SQL I have in the query right now is

SELECT projects.[Project Name], projects.[Client Name], projects.[Service Type], Format(projects.[Date Completed],"dd mmm") AS OneDate, Format(projects.[Date Completed],"yyyy") AS TwoDate, projects.[Final Payment]
FROM projects
WHERE (((Format(projects.[Date Completed],"dd mmm")) Between "01 Jan" And "31 Dec") And (Format(projects.[Date Completed],"yyyy")=Forms![queries]!cboYear_List));

(I changed the name of my form from "queries/reports" to "queries" to make it easier on myself and it changed it in the SQL automatically just to let you know.)
0
 
dannywarehamCommented:
I'll output all fields, so you can put anything into a report...OK?
Try this:

SELECT *
FROM projects
WHERE (((Format(projects.[Date Completed],"dd mmm")) Between "01 Jan" And "31 Dec") And (Format(projects.[Date Completed],"yyyy")=Forms![queries]!cboYear_List));
0
 
dannywarehamCommented:
Slightly shorter:

SELECT *
FROM projects
WHERE (((Format([Date Completed],"dd mmm")) Between "01 Jan" And "31 Dec") And (Format([Date Completed],"yyyy")=Forms![queries]!cboYear_List));
0
 
mrEAuthor Commented:
I still get no records.
0
 
mrEAuthor Commented:
plus it's not formatted correctly still
0
 
dannywarehamCommented:
Could you possibly send me a winzipped copy (address in profile)?

Also, what output would you like to see...? (columns names etc)
0
 
mrEAuthor Commented:
I'm sorry. OneDate and TwoDate aren't even columns in the results anymore- so i suppose it doesn't matter, eh?
I can email it to you, yes.
0
 
mrEAuthor Commented:
I'm actually going to be uploading to my server. I'll email you the address so you can download it.
0
 
d_a_hCommented:
Another way of doing it:

Create a form with a textbox (called txtYear) and a command button (cmdOK)

Teel the user to type in the year (4 digits) and click OK

Code for OnClick Event of cmdOK

Private Sub cmdOK_Click()
   
    Dim tmpYear As Date
    If Len(Me.txtYear) > 4 Then
    Else
   
    tmpYear = "01/01/" & Me.txtYear
   
    Me.txtYear = tmpYear
    End If

End Sub

Now in your query use this:

SELECT projects.[Project Name], projects.[Client Name], projects.[Service Type], projects.[Date Completed], projects.[Final Payment]
FROM projects
WHERE (((projects.[Date Completed]) Between Forms![Name of Form]!txtYear AND DateAdd("d",-1, DateAdd("yyyy",1,Forms![Name of Form]!txtYear))));

**NOTE** Change [Name of Form] in SQL to whatever you have called it.

If you want as an upgrade for your form:  Hide txtYear and create another box for people to type in (txtInput).  Amend the code for the command button.  Add another button for Year up (cmdUp)

Private Sub cmdUp_OnClick()
Dim tmpDate as date
tmpDate = me.txtYear
tmpDate = DateAdd("yyyy",1,tmpDate)
me.txtYear = tmpDate
End Sub

And do the same for another button for Year down (cmdDown) and change the DateAdd to ("yyyy", -1, tmpDate)

HTH

0
 
mrEAuthor Commented:
Is there some way to make it go back to how it used to work where the user had to type in the year they wanted? Because that would be a great idea. All we'd have to do is re-name what it said so that they knew what they were supposed to type in there for the year rather than knowing what "cboYear_List" meant.
0
 
mrEAuthor Commented:
Whoa. the solution you just typed out is more confusing than what we were doing.
Can we talk on AIM or MSN? this would make it so much easier.
0
 
mrEAuthor Commented:
oops i didn't realize it was someone else who posted that. my fault.
0
 
d_a_hCommented:
Ought to be straightforward.  If you get stuck then post.  Am presuming that you have created forms before if not then will send more detailed instructions

HTH
0
 
mrEAuthor Commented:
d_a_h:
Yes I have made forms before. I made the mistake of not realizing it was someone other than danny who made that post (it was you) and I thought it was him telling me I should do what you suggested...

I'm sorry for the confusion.
Danny is really involved now and I don't want to try another way before he says that his way can't be done- and I'm sure his way can be done. But I appreciate your suggestion, nonetheless. Thank you.
0
 
d_a_hCommented:
No probs.
0
 
dannywarehamCommented:
mrE (and d_a_h)

I've posted you back an example.
For those playing along at home, what I've don is create a query like this:

SELECT *
FROM projects
WHERE (((Format([Date Completed],"dd mmm")) Between "01 Jan" And "31 Dec") AND ((Format([Date Completed],"yyyy"))=[forms]![queries]![cboYear_List]));

(which is exactly as posted earlier)

Then, as an example, I've used a subform to show the result (mrE had a query object open before).
0
 
mrEAuthor Commented:
I still need to be able to make a report out of this, though. :-(
0
 
mrEAuthor Commented:
I found a solution.
The query I made uses the following SQL statement:

SELECT*
FROM projects
WHERE (((Format([Date Completed],"dd mmm")) Between "01 Jan" And "31 Dec") AND ((Format([Date Completed],"yyyy"))=[For what year would you like to see the income?]));

It causes a "dialogue box" to ask the user what year they would like to get the results... and it brings back everything I need. This is exactly what I wanted- and I have you (danny) to thank. Thank you.
0
 
dannywarehamCommented:
Glad to help, Erik

:-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 18
  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now