Input Field Box

John Jnr

Registered User.
Local time
Today, 14:39
Joined
May 2, 2002
Messages
36
Help please.
All I want to do is on a query prompt the user to select the year field. I have a number of year fields containing spend(£) data. I have tried a number of expresions in the field entry grid with no success.
 
If I interpret your question correctly, you need to use a parameter query. In the criteria row of the year field type

[Please enter a year:]

as it looks above. Then run the query and you will be prompted to enter a year. All matching records will be displayed. You can then base a report on this query and so when you open the report you are prompted the same way, except the results are in a nicer looking format!

HTH

Scott.
 
Thanks Scott for your reply but that doesn't help.
My table the query refers is linked to an excel spreadsheet. The left hand column is a list of suppliers. The top row is years. The data is spend of supplier in that year:
2002 2001 2000
Supplier Name 300,000 200,000 100,000
Supplier Name x 245,000 145,000 167,000

All I want the query to do is ask the user for a year or any year in the table. The query should then list all the suppliers in that year with the relative spend.

This is doing my head in as I know this must be possible?

Thanks in advance.
 
John

Not sure if this is what you want but you could create a pop up form with a combo box which will look up the values in the year field from your table then in the query enter an expression say frm[yourpopupfrmName]![Yourcomboboxname] and make it unbound. You could then have a report so when you open the report you get the pop up form to display firstly then you would make your selection fromthe combo box and the results would display in your report.

If this sounds like what you're after and you need help with this just post back.
 
Hayley,
Thanks for the response and I do need a little extra help.
My table is as follows with the top row as the Year field names (column field names)and the suppliers as the other row headings as follows:
2002 2001 2000
Supplier x £3000 £2000 £1000
Supplier y £1000 £900 £800
Supplier z £500 £200 £100

etc.

The spend is the data. All I want is a query that pulls up a requester so that the user can input a year for the query to show the spend data. I have tried putting [Enter Year:] in the query field name but it just puts the entered year data in every field next to the supplier name.
Your suggestion sounds interesting but where do I put the expresion? In the field box or the criteria?

I want to do this as the table is linked to an external Excel file. The data is updated every quarter and year. I need the user to be able to update the query and the report to enable flexibility. Is there a way that I can get the query to pull data via column numbers rather than specific data field names?

Thanks in advance.
 
Working with data in access is different to Excel, you do not need separate fields for each year, one field to indicate the year is the correct approach, if you want to display data with each year displayed across the top of the datasheet use a crosstab query, a simple parameter query using one year field will give the results you require.
 
Rich,
I have changed the table data as:
Supplier
Year
Spend
Created a crosstab query, and that work ok.
What I want to do is then create a report in which the user selects the year span to report from i.e. from 2001 to 1997.
I created a parameter query limked to the crosstab and access would allow me to select year, only the individual years? I hope this makes sense as I have no hair left.

Cheers

John
 

Users who are viewing this thread

Back
Top Bottom