Crosstab Query

[crislim1113]

Hi good day to all. in my crosstab query. i want to include the sorted out requested delivery date(but it will not show). It was like slicer in excel.
this is my query

and here is the combobox where i will choose the month. Can someone help me with these. It will be greatly appreciated.

Regards,
Cris

 

[Rene vK]

You don't have RequestedDeliceryDate in your query and for the combo, just make a list! januari; februari; march; etc

 

[arnelgp]

what is the Rowsource of the Combo?
does the combobox also have Year?

 

[CJ_London]

perhaps include the month of the delivery date in your cross tab query

expr1:month([requestedDeliveryDate])

change the Total row to 'WHERE'

in criteria put something like

forms!mainboard!cboMonth

note you also need to declare the reference in parameters - on the ribbon, select the parameters button and add it in there - presumably as type integer (1 for january)

If it is text (i.e. January) then declare it a text

You will no doubt also need some way of stating the year as well, but not at all clear from your data how you might do that

 

[crislim1113]

what is the Rowsource of the Combo?
does the combobox also have Year?

This one sir.. I think I must add year combo sir?

Regards,
Cris

 

[arnelgp]

you can have a single combo for Month-Year.
or you can add another combo for the Year.

 

[crislim1113]

perhaps include the month of the delivery date in your cross tab query

expr1:month([requestedDeliveryDate])

change the Total row to 'WHERE'

in criteria put something like

forms!mainboard!cboMonth

note you also need to declare the reference in parameters - on the ribbon, select the parameters button and add it in there - presumably as type integer (1 for january)

If it is text (i.e. January) then declare it a text

You will no doubt also need some way of stating the year as well, but not at all clear from your data how you might do that

Hi Sir,
am I doing it right?

Please bear with me ..

Regards,
Cris

 

[crislim1113]

Hi Sir,

I would like to export only the month of the current year. I think I will put another field for year only..
Thanks

you can have a single combo for Month-Year.
or you can add another combo for the Year.

 

[arnelgp]

if the intention is for the Current year only, forget the Year-combo.
if others will be using the db, just guide the user that the xtab is for the currentyear only.

create New Column on your xtab query (and show it as another Row Heading):

Expr1: MonthName(Month([requestedDeliveryDate])) & "-" Year(Date())
Criteria: [Forms]![YourForm]![yourCombo] & "-" Year(Date())

 

[crislim1113]

if the intention is for the Current year only, forget the Year-combo.
if others will be using the db, just guide the user that the xtab is for the currentyear only.

create New Column on your xtab query (and show it as another Row Heading):

Expr1: MonthName(Month([requestedDeliveryDate])) & "-" Year(Date())
Criteria: [Forms]![YourForm]![yourCombo] & "-" Year(Date())

Hi sir, it does work in my other query, but not in my crosstab.. sir am I correct? btw thank you very much sir

 

[arnelgp]

remove the new Row Heading, instead use "requestedDeliveryDate" as the Row Heading.

 

[crislim1113]

Hi sir ,
Like this? it doesn't go to datasheet view sir. thanks