Cross Tab query question

[BenTitus]

Hello i am having a problem with cross tabs. I have never used crosstabs in access before and my boss just asked me if i could change around their crosstab. The way things are ran now is someone each week must type in the query, the dates they want the data to be between. They want to be able to be prompted to enter a start and an end date for the range. I tried adding a BETWEEN [Start Date] and [END DATE] but get an error saying problem obtaining data.
The database they use seems to have some problems which may be my problem but i dont think. The SQL they have now for the query the cross tab is built off of is:

SELECT DISTINCTROW Demand.[Part#], Parts.[Part Description], Demand.[Ship Date], Demand.Qty, [Ship Date].[Day of the Week], Parts.[Cycle Time], Parts.[Setup Time], [Demand]![Qty]*[Parts]![Cycle Time] AS Expr1
FROM (Demand INNER JOIN Parts ON Demand.[Part#] = Parts.[Part#]) INNER JOIN [Ship Date] ON Demand.[Ship Date] = [Ship Date].[Ship Date]
WHERE (((Demand.[Part#])='040001-001' Or (Demand.[Part#])='040011-001' Or (Demand.[Part#])='040021-001' Or (Demand.[Part#])='073542-001' Or (Demand.[Part#])='073552-001' Or (Demand.[Part#])='073562-001' Or (Demand.[Part#])='073572-001' Or (Demand.[Part#])='073582-001' Or (Demand.[Part#])='040001-002' Or (Demand.[Part#])='040011-002' Or (Demand.[Part#])='040021-002' Or (Demand.[Part#])='073543-001' Or (Demand.[Part#])='073553-001' Or (Demand.[Part#])='073563-001' Or (Demand.[Part#])='073573-001' Or (Demand.[Part#])='073583-001' Or (Demand.[Part#])='040003-002' Or (Demand.[Part#])='040013-002' Or (Demand.[Part#])='040023-002' Or (Demand.[Part#])='040038-003' Or (Demand.[Part#])='040058-003' Or (Demand.[Part#])='040078-003' Or (Demand.[Part#])='040098-003' Or (Demand.[Part#])='040118-003' Or (Demand.[Part#])='040039-003' Or (Demand.[Part#])='040059-003' Or (Demand.[Part#])='040079-003' Or (Demand.[Part#])='040099-003' Or (Demand.[Part#])='040119-003' Or (Demand.[Part#])='123456'));
Then in the cross tab under date the date they write >=#5/26/2003# And <=#6/1/2003# and change it each week
Thanks in advance

 

[dodge20]

When you tried to add between [Start Date] and [END DATE] did you specify what was between those.

Something like

and Demand.[ship date] between Start Date] and [END DATE]

is probably what you want.

Dodge20

 

[BenTitus]

When I entered Demand.[ship date] between [Start Date] and [END DATE] in the criteria for ship date and created the cross tab i got an error saying Microsoft Jet Database engine does not recognize '[Start Date]' as a valid field name or expression.

 

[dodge20]

What does the sql look like now? Try pasting this.

SELECT DISTINCTROW Demand.[Part#], Parts.[Part Description], Demand.[Ship Date], Demand.Qty, [Ship Date].[Day of the Week], Parts.[Cycle Time], Parts.[Setup Time], [Demand]![Qty]*[Parts]![Cycle Time] AS Expr1
FROM (Demand INNER JOIN Parts ON Demand.[Part#] = Parts.[Part#]) INNER JOIN [Ship Date] ON Demand.[Ship Date] = [Ship Date].[Ship Date]
WHERE Demand.[ship date] between [Enter Start Date] and [Enter END DATE]
AND (((Demand.[Part#])='040001-001' Or (Demand.[Part#])='040011-001' Or (Demand.[Part#])='040021-001' Or (Demand.[Part#])='073542-001' Or (Demand.[Part#])='073552-001' Or (Demand.[Part#])='073562-001' Or (Demand.[Part#])='073572-001' Or (Demand.[Part#])='073582-001' Or (Demand.[Part#])='040001-002' Or (Demand.[Part#])='040011-002' Or (Demand.[Part#])='040021-002' Or (Demand.[Part#])='073543-001' Or (Demand.[Part#])='073553-001' Or (Demand.[Part#])='073563-001' Or (Demand.[Part#])='073573-001' Or (Demand.[Part#])='073583-001' Or (Demand.[Part#])='040003-002' Or (Demand.[Part#])='040013-002' Or (Demand.[Part#])='040023-002' Or (Demand.[Part#])='040038-003' Or (Demand.[Part#])='040058-003' Or (Demand.[Part#])='040078-003' Or (Demand.[Part#])='040098-003' Or (Demand.[Part#])='040118-003' Or (Demand.[Part#])='040039-003' Or (Demand.[Part#])='040059-003' Or (Demand.[Part#])='040079-003' Or (Demand.[Part#])='040099-003' Or (Demand.[Part#])='040119-003' Or (Demand.[Part#])='123456'));


Dodge20

 

[GingerR]

in a crosstab query, you have to put those criteria into the PARAMETERS.

in the query design, right-click in the grey part up top and choose PARAMETERS.
enter in [START DATE] and it's type, and [END DATE] and its type.

that should do it.

 

[BenTitus]

Is there a way to make it Between [Start Date] and [End Date] because right now it does not keep the values within the parameters

 

[BenTitus]

Sorry i didnt mean to type that. My problem is that the parameter works but does not put more than one item on each line so it repeats the part number for each day of the week instead of putting them all side by side.

 

[BenTitus]

Sorry for bugging everyone. I got it working and thanks for your help

 

[micereza]

BenTitus,

We are having the same problem that you had... How did you enter Between [Start Date] and [End Date] as a parameter in your crosstab?

Note: We are intermediate Access users (no speak SQL).

 

[dhookom]

To use parameters, read GingerR's reply. If you don't want to Group By the dates then change the &quot;Group By&quot; to &quot;Where&quot;.

Duane
MS Access MVP