date formatting problem

[benalken8]

Hello,

Hopefully i can get some help on this problem.

i have a select queriy that shows total amount for jobs delivered. I have set a [start date] and [end date] criteria so that i can show totals between two dates. the problem i have is that i want to use a combo box on a form to pick the month an year rather than choose between two dates. The problem i am having is that i cannot change the format of the field that has the dates in. i am wanting to change it from dd/mm/yyyy to mmm yy so that my combo box can have a list of months an yrs ie. jan 09. which i can then use for the criteria in my quiery.


Any help would be most appriciated

[MMcCarthy]

You don't need to change the format. In the drop down box for the month set it to two columns. The first would hold the month number and be hidden (this would be the value returned by the dropdown selection) and the second would have the month in words and would be shown in the display.

Then change the field on your query to be ...

Month([DateFieldName])

with the criteria
Forms!FormName! MonthDropDownNa me

and set up a second column ...

Year([DateFieldName])

with the criteria
Forms!FormName! YearDropDownNam e

If you are having problems following this post the full SQL code of your query and I'll show you what I mean.

[benalken8]

Thanks for the help i am still struggling to get it to work.

when i try and run my query it gives me an error message.

" you tried to execute a query that does not include the specified expression 'Month([Delivery Date])' as part of an aggergate Function"

i have copied the Sql Below.

SELECT tblMainJobTable.Customer, Sum([Quantity]*[Price]) AS Total, Month([Delivery Date]) AS Expr1, Year([Delivery Date]) AS Expr2
FROM tblMainJobTable
WHERE (((tblMainJobTable.[Job Card Check])="C"))
GROUP BY tblMainJobTable.Customer
HAVING (((Month([Delivery Date]))=[Forms]![frmtotalsmonth]![Combo3]) AND ((Year([Delivery Date]))=[forms]![frmtotalsmonth]![Combo4]))
ORDER BY tblMainJobTable.Customer;

[NeoPa]

Your first problem is that you are putting (have put) your selection criteria within the HAVING clause rather than the WHERE clause where it should be.

How exactly to formulate your criteria is complicated by the fact that we don't know what Combo3 & Combo4 are supposed to be, nor in what format they return their data. Particularly as your question implied From & To dates rather than a single date specified as separate month and year.

If you can explain what data you're really dealing with (control names would be helpful) then I'm sure we can help you resolve your issue, either using separate Month() & Year(), or my personal preference, using Format(). The concept is the same with both.

Welcome to Bytes!

[benalken8]

ok thanks.

i have a 'Customer' field which contains Customer names. i have a 'Job Card Check' field which shows if a job is complete or not. i.e 'C' Means complete. and i have a 'Delivery Date' field which holds the date which a job was delivered formatted. dd/mm/yyyy. The aim of this query i am creating is to show the the total in £ of all jobs delivered in a chosen month and group them by customer so that it gives me on figure for each customer. i have been able to make it work using Between[start Date]and [End Date]. But i would like to run the query from a form with two combo boxes or drop down boxes. one will contain the months i.e jan, feb mar. and one the yr i.e 09, 10 , 11. so that when i run my form i can select the month and year and they will filter my query to show that month of that year and the relative totals.

sorry if it is a little confusing

[NeoPa]

Let me just confirm what I think you're saying here :

1. You would like to replace the Between A And B concept with the facility simply to select a specific month from the two ComboBoxes.
2. The Month ComboBox would return text strings reflecting the 3 character abbreviations of each month ("Jan","Feb","M ar", etc).
3. The Year ComboBox would return text strings (not numbers as such) reflecting the 2-digit value for the year.

Are all these understandings correct?

[benalken8]

yes that is correct sorry if i didnt explain properley.
i would then want to run the combo boxes on a form so that once the month and year has been selected the form will refresh showing the relavant info.

[NeoPa]

No. That was clear enough. I was just being cautious as other ideas had been floated around and I wanted to confirm before proceeding. I find things work better that way. Anyway, on to the issue...

The comparison can either be done by converting what you have (The ComboBox values) to dates (from & to), or by converting the date value in the record to a string reflecting the month. The latter seems the easier approach to me so :

WHERE Format([Delivery Date],'yymmm')=cboYear & cboMonth

[benalken8]

thanks

now on my form i have the two combo boxs named as they are in the query and i have added a command button to run the query once i have chose the desired month and year from the combo boxes. i have placed the two combo boxes and the button in the form header. All the fields like customer and total are in the Detail section.


the problem now is that when i run the query weather it be from the from or from the query screen in runs the query but just show the Field headers of the query and no data. this happens even though i have selected the desiered month and year in the combo boxes.

cheers

Ben

[NeoPa]

And what do you have in your query now? Have you changed it at all? Have you updated the WHERE clause to match my suggestion?

It's really very hard to help you if you don't communicate with me clearly. Guessing what you may, or may not, have changed is not a clever way for me to proceed.

PS. Looking at my suggested WHERE clause from post #8, it seems I may have got the ComboBox references wrong. They should have read :

WHERE Format([Delivery Date],'yymmm')=[Forms]![frmTotalsMonth].[cboYear] & 
                                      [Forms]![frmTotalsMonth].[cboMonth]

I'm working on the understanding that the ComboBoxes are both on your [frmTotalsMonth] form, of course.