operator selected date range

[jmd0252]

I know,,, that somewhere you boys and girls have answered this before, but as I am an RPG programmer, working on an access project. I need to build a query of the file, based on a beginning and ending date range. I know how to prompt for a varible, like an equipment number, or order number. But have no idea how to prompt for a begining and endng date. Any and all help is appreicated..

 

[Zonie32]

You could use "Between" in the date field.

Between [BeginDate] and [EndDate]

 

[jmd0252]

OK,, when I try that I get an error that says the expression is too complex,,, try assigning parts of the expreesion to variables.. Any ideas???

 

[SkipVought]

Hi,

When the user enters data at the begindate/enddata propmts it's TEXT. Convert to Date
[tt]
Between Format([BeginDate], "GeneralDate") And Format([EndDate], "GeneralDate")
[/tt]
Also...

Are you sure that the user always enters the correct date format ie year (2 or 4 digits???), month, day IN THAT ORDER or whatever order?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jmd0252]

Well tries that,,, and since I am the operator, I know I am entering a 4 diget year,, it come up and gives me the same error message.. More ideas??????

 

[dhookom]

How about posting the SQL view?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SkipVought]

Please post your full SQL statement.



Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jmd0252]

ok guys I have to ask a stupid question,,, where do I find the SQL statement. Remember I know RPG,, and access a little. I designed a query and put the above statement into the "criteria",for the field I want to check for the date range, tried it as written, and then also, substituting the filed name for "generaldate". So from your question,, you are going to tell me I am doing the wrong thing,,, but that is ok,,, tell away.

 

[SkipVought]

OK You're working from a query grid.

So you have something like this in your criteria?

[Req_Date] Between [Start] And [end]

Could you paste it here?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jmd0252]

Actually I had exactly what was in the 4th post down,, also tried what was in the second,, but here is what is in there now.. Between Format([BeginDate],"""date_in""") And Format([EndDate],"""date_in"""), I had substituted the "date_in", whihc is the field name.. It will prompt fore the date,, but then gives me the error message. Would it be easier to write an SQL statemnt,, for the date prompting??

 

[SkipVought]

[tt]
[date_in] Between [BeginDate] And [EndDate]
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jmd0252]

Ok,, that does not work either,,, lets start over maybe I am doing something completely worng,,, and that is not out of the question. 1st designed a query that outputs a summary file totaled by site code, by customer, has 3 totals, tons, material dollars, and total dollars, sort by site, by descending dollars,, so file is biggest buyers by site, by total dollars. Now carry this one step furthur,,want to do the same thing,,but able to prompt the operator, so that the summary file, is for a requested date range. so I did a copy, paste and am trying to modify the original query,, which does not seem to want to be modified. So how much trouble have I cause by my questionb?? and do I need to break the query into 2 parts, one to choose the date range, and one to do the summary?

 

[dhookom]

Maybe start by telling us what your table structure is with some sample data. Then show us what you expect in your final results.

To find the SQL view of your query, select View|SQL while in the query design.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jmd0252]

customer and product file,, pretty standard. transaction file,, is quarry (crushed limestone) ticket information. Tickets being,, 5 different quarries, identified by site number, selling crushed limestone, and masonry products.
files are joined transaction to customer, by customer no, joined to product by site and product code. Try to provide ways to generate sales reports. Original quarry software has Dbase files structure,, ie DBF files. so we export info into tables, where we can write sales reports. TA-DA,, So first need is top ten customers by site, determined by total sales dollars. Have not yet figured out how to get only 10,,, but first query gives me by sales dollars by site, with the largest at the top. so now trying to provide way for operator to choose date range for sales info. By the way,, if I "hardwire",, the dates into a query if give me the range I want,, just cannot figure out how to prompt the operator.

 

[SkipVought]

What was wrong with
[tt]
[date_in] Between [BeginDate] And [EndDate]
[/tt]
in the date_in criteria?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jmd0252]

it is gives me the same error message as before.??????? that the expression is too complex, and that I need to assign to variables,, go figure.?????

 

[lespaul]

could you switch to the SQL view as described above and paste your SQL query?

You may also need to add the parameters (I'd have to search TT to find out how, I don't have Access on this machine), but there should be a command in the menu choices with the query design to add parameters.

If it works with the dates hard coded, you should be able to replace the first date with [Enter start date] and the last date with [Enter end date] (be sure to have the []!) and get prompted to enter them.

Leslie

 

[jmd0252]

SELECT rock.*
FROM rock
WHERE (((rock.DATE_IN) Between #1/1/2004# And #3/31/2004#));


ok that is one.. outputting the whole file,,, just filtering by dates.

SELECT rock.STAT_CODE, rock.CUST_CODE, Customer.NAME, Sum(rock.NET_TONS) AS SumOfNET_TONS, Sum(rock.MAT_CHGE) AS SumOfMAT_CHGE, Sum(rock.TOTAL_CHGE) AS SumOfTOTAL_CHGE
FROM Customer INNER JOIN rock ON Customer.CODE = rock.CUST_CODE
GROUP BY rock.STAT_CODE, rock.CUST_CODE, Customer.NAME
ORDER BY rock.STAT_CODE, Sum(rock.TOTAL_CHGE) DESC;

that is the summary query,, the original one I was trying to modify.

and I do not disagree,,, I would think that access, would just substitute the paramater for the date,, and go,, might try one at a time, and see what happens..

 

[SkipVought]

les
[tt]
[Req_Date] Between [Start] And [end]
[/tt]
prompts for 2 entries, Start & end. Req_Date is a table field.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[lespaul]

So, if you run this:

SELECT rock.STAT_CODE, rock.CUST_CODE, Customer.NAME, Sum(rock.NET_TONS) AS SumOfNET_TONS, Sum(rock.MAT_CHGE) AS SumOfMAT_CHGE, Sum(rock.TOTAL_CHGE) AS SumOfTOTAL_CHGE
FROM Customer INNER JOIN rock ON Customer.CODE = rock.CUST_CODE
WHERE (((rock.DATE_IN) Between [Enter Start Date] And [Enter End Date]))
GROUP BY rock.STAT_CODE, rock.CUST_CODE, Customer.NAME
ORDER BY rock.STAT_CODE, Sum(rock.TOTAL_CHGE) DESC;

you don't get two parameter prompts?

Now, like Skip says, this is going to prompt for TWO parameters. You can build a form to have the user enter this information and then pass the values to the query (slightly different structure for that and I think maybe we should get it working first!)

You also mentioned earlier in the thread that you need to select a certain number of the top sales:

SELECT TOP 10 rock.STAT_CODE, rock.CUST_CODE, Customer.NAME, Sum(rock.NET_TONS) AS SumOfNET_TONS, Sum(rock.MAT_CHGE) AS SumOfMAT_CHGE, Sum(rock.TOTAL_CHGE) AS SumOfTOTAL_CHGE
FROM Customer INNER JOIN rock ON Customer.CODE = rock.CUST_CODE
WHERE (((rock.DATE_IN) Between [Enter Start Date] And [Enter End Date]))
GROUP BY rock.STAT_CODE, rock.CUST_CODE, Customer.NAME
ORDER BY rock.STAT_CODE, Sum(rock.TOTAL_CHGE) DESC;

will only return the top 10 entries. If their are any ties, then you will only have 9 different totals but 10 different records.

HTH

Leslie