parameterized cross tab query 1

[Pampers]

Hi everyone,
I have a cross tab query with parameters (startdate & enddate). All ok. Now I trie to set the parameters to refer to a form, so the input is more user friendly (dates set with a calendar). But that doesn't seem to work... Message is: "Engine does not recognize ... as a valid field expression..."

Any suggestions.



Pampers
Keeping it simple can be complicated

 

[PHV]

Could you please post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

PARAMETERS startdate DateTime, enddate DateTime;
TRANSFORM Count(lstContainerTypes.ContainerCode) AS CountOfContainerCode
SELECT lstPorts.PortRegion
FROM tblVoyageNo INNER JOIN ((tblContainers INNER JOIN lstContainerTypes ON tblContainers.ContainerTypeID = lstContainerTypes.ContainerTypeID) INNER JOIN ((lstPorts INNER JOIN tblBolImport ON lstPorts.PortID = tblBolImport.PortOfLoadID) INNER JOIN tblPackagesImp ON tblBolImport.BolImportID = tblPackagesImp.BolImportID) ON tblContainers.ContainerID = tblPackagesImp.ContainerID) ON tblVoyageNo.VoyageID = tblBolImport.VoyageID
WHERE (((tblBolImport.PartialContainerFollow)=False) AND ((tblBolImport.BolType)="import"))
GROUP BY lstPorts.PortRegion
PIVOT lstContainerTypes.ContainerType;

Pampers
Keeping it simple can be complicated

 

[PHV]

In your PARAMETERS instruction, replace startdate and enddate with the full qualified name of the controls, eg:
[Forms]![your form]![startdate control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Hi PHV,
Very Nice. Works great.


And off course you are always welcome to stay our place at curacao! Plus free diving lessons.


Pampers
Keeping it simple can be complicated

 

[Pampers]

Forgot to post the working code:

PARAMETERS [Forms]![DialogPrintAnalysis]![StartDate] DateTime, [Forms]![DialogPrintAnalysis]![EndDate] DateTime;
TRANSFORM Count(lstContainerTypes.ContainerCode) AS CountOfContainerCode
SELECT lstPorts.PortRegion
FROM tblVoyageNo INNER JOIN ((tblContainers INNER JOIN lstContainerTypes ON tblContainers.ContainerTypeID = lstContainerTypes.ContainerTypeID) INNER JOIN ((lstPorts INNER JOIN tblBolImport ON lstPorts.PortID = tblBolImport.PortOfLoadID) INNER JOIN tblPackagesImp ON tblBolImport.BolImportID = tblPackagesImp.BolImportID) ON tblContainers.ContainerID = tblPackagesImp.ContainerID) ON tblVoyageNo.VoyageID = tblBolImport.VoyageID
WHERE (((tblBolImport.PartialContainerFollow)=False) AND ((tblBolImport.BolType)="import") AND ((tblVoyageNo.EtaDay) Between [Forms]![DialogPrintAnalysis]![StartDate] And [Forms]![DialogPrintAnalysis]![EndDate]))
GROUP BY lstPorts.PortRegion
PIVOT lstContainerTypes.ContainerType;

Pampers
Keeping it simple can be complicated