Date and Parameter question.

D

Dan

Hi I am having a problem in a query I am trying to pull. I have a date feild
that I am querying and in the criteria I have this.
<=Date()-[Enter number Of Days] when I run this it gives me an ODBC error.
If I changeit to this <=Date()-30 Than it works and pulls just everything 30
days or older. Is there anyway to make the parameter value work in this type
of scenario?
 
D

Dan

Karl Thanks for the response. I tried this and it isn't working either. I am
still getting an ODBC error. I have attached the SQL to maybe shed some
additional light on the subject. Thanks

SELECT [MA#FILECEN_MAPILOC].ILHOSP AS Hosp, [MA#FILECEN_MAPILOC].ILLOCN AS
Locn, [MA#FILECEN_MAPILOC].ILITEM AS [Item#], [MA#FILECEN_MAPCITM].CTDESC AS
[Desc], [MA#FILECEN_MAPCITM].[CTMAN#] AS [Manu#], [MA#FILECEN_MAPILOC].ILBIN1
AS Bin,
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTBUNT],([MA#FILECEN_MAPILOC]![ILONHD]/1),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT2],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV2]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT3],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV3]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT4],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV4]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT5],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV5]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT6],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV6])))))))
AS [On Hand Qty], [MA#FILECEN_MAPVDIV].VXDNAM AS [Vend Name],
[MA#FILECEN_MAPILOC].ILFLG2 AS [Value Class], [MA#FILECEN_MAPILOC].ILUDAT AS
[Last Used Date], [MA#FILECEN_MAPILOC]![ILYQOT]-[MA#FILECEN_MAPILOC]![ILYQIN]
AS [YTD Usage], [MA#FILECEN_MAPCSPL].CSZPFG
FROM (([MA#FILECEN_MAPCITM] INNER JOIN [MA#FILECEN_MAPILOC] ON
[MA#FILECEN_MAPCITM].CTITEM = [MA#FILECEN_MAPILOC].ILITEM) INNER JOIN
[MA#FILECEN_MAPCSPL] ON ([MA#FILECEN_MAPILOC].ILHOSP =
[MA#FILECEN_MAPCSPL].CSHOSP) AND ([MA#FILECEN_MAPILOC].ILITEM =
[MA#FILECEN_MAPCSPL].CSITEM)) INNER JOIN [MA#FILECEN_MAPVDIV] ON
[MA#FILECEN_MAPCSPL].CSVID = [MA#FILECEN_MAPVDIV].VXVID
WHERE ((([MA#FILECEN_MAPILOC].ILHOSP)=[Enter Hosp Number]) AND
(([MA#FILECEN_MAPILOC].ILLOCN)=[Enter Location]) AND
(([MA#FILECEN_MAPILOC].ILUDAT)<=Date()-CVDate([Enter number Of Days])))
ORDER BY [MA#FILECEN_MAPILOC].ILUDAT DESC;


KARL DEWEY said:
Try this --
<=Date()-CVDate([Enter number Of Days])

--
Build a little, test a little.


Dan said:
Hi I am having a problem in a query I am trying to pull. I have a date feild
that I am querying and in the criteria I have this.
<=Date()-[Enter number Of Days] when I run this it gives me an ODBC error.
If I changeit to this <=Date()-30 Than it works and pulls just everything 30
days or older. Is there anyway to make the parameter value work in this type
of scenario?
Click to expand...
Click to expand...
 
D

Duane Hookom

IMO, parameter queries are not appropriate user interface however... you
shouldn't convert the number to a date when you really want to subtract an
integer from today's date.
(([MA#FILECEN_MAPILOC].ILUDAT)<=Date()-[Enter number Of Days]))
or
(([MA#FILECEN_MAPILOC].ILUDAT)<=Date()-Int([Enter number Of Days])))

--
Duane Hookom
Microsoft Access MVP


Dan said:
Karl Thanks for the response. I tried this and it isn't working either. I am
still getting an ODBC error. I have attached the SQL to maybe shed some
additional light on the subject. Thanks

SELECT [MA#FILECEN_MAPILOC].ILHOSP AS Hosp, [MA#FILECEN_MAPILOC].ILLOCN AS
Locn, [MA#FILECEN_MAPILOC].ILITEM AS [Item#], [MA#FILECEN_MAPCITM].CTDESC AS
[Desc], [MA#FILECEN_MAPCITM].[CTMAN#] AS [Manu#], [MA#FILECEN_MAPILOC].ILBIN1
AS Bin,
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTBUNT],([MA#FILECEN_MAPILOC]![ILONHD]/1),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT2],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV2]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT3],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV3]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT4],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV4]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT5],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV5]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT6],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV6])))))))
AS [On Hand Qty], [MA#FILECEN_MAPVDIV].VXDNAM AS [Vend Name],
[MA#FILECEN_MAPILOC].ILFLG2 AS [Value Class], [MA#FILECEN_MAPILOC].ILUDAT AS
[Last Used Date], [MA#FILECEN_MAPILOC]![ILYQOT]-[MA#FILECEN_MAPILOC]![ILYQIN]
AS [YTD Usage], [MA#FILECEN_MAPCSPL].CSZPFG
FROM (([MA#FILECEN_MAPCITM] INNER JOIN [MA#FILECEN_MAPILOC] ON
[MA#FILECEN_MAPCITM].CTITEM = [MA#FILECEN_MAPILOC].ILITEM) INNER JOIN
[MA#FILECEN_MAPCSPL] ON ([MA#FILECEN_MAPILOC].ILHOSP =
[MA#FILECEN_MAPCSPL].CSHOSP) AND ([MA#FILECEN_MAPILOC].ILITEM =
[MA#FILECEN_MAPCSPL].CSITEM)) INNER JOIN [MA#FILECEN_MAPVDIV] ON
[MA#FILECEN_MAPCSPL].CSVID = [MA#FILECEN_MAPVDIV].VXVID
WHERE ((([MA#FILECEN_MAPILOC].ILHOSP)=[Enter Hosp Number]) AND
(([MA#FILECEN_MAPILOC].ILLOCN)=[Enter Location]) AND
(([MA#FILECEN_MAPILOC].ILUDAT)<=Date()-CVDate([Enter number Of Days])))
ORDER BY [MA#FILECEN_MAPILOC].ILUDAT DESC;


KARL DEWEY said:
Try this --
<=Date()-CVDate([Enter number Of Days])

--
Build a little, test a little.


Dan said:
Hi I am having a problem in a query I am trying to pull. I have a date feild
that I am querying and in the criteria I have this.
<=Date()-[Enter number Of Days] when I run this it gives me an ODBC error.
If I changeit to this <=Date()-30 Than it works and pulls just everything 30
days or older. Is there anyway to make the parameter value work in this type
of scenario?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

TRY the following. I've edited the criteria and the from clause.

SELECT [MA#FILECEN_MAPILOC].ILHOSP AS Hosp
, [MA#FILECEN_MAPILOC].ILLOCN AS Locn
, [MA#FILECEN_MAPILOC].ILITEM AS [Item#]
, [MA#FILECEN_MAPCITM].CTDESC AS [Desc]
, [MA#FILECEN_MAPCITM].[CTMAN#] AS [Manu#]
, [MA#FILECEN_MAPILOC].ILBIN1 AS Bin
, IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTBUNT]
,([MA#FILECEN_MAPILOC]![ILONHD]/1),
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT2],
([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV2]),
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT3],
([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV3]),
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT4],
([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV4]),
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT5],
([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV5]),
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT6],
([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV6])))))))
AS [On Hand Qty]
, [MA#FILECEN_MAPVDIV].VXDNAM AS [Vend Name]
, [MA#FILECEN_MAPILOC].ILFLG2 AS [Value Class]
, [MA#FILECEN_MAPILOC].ILUDAT AS [Last Used Date]
, [MA#FILECEN_MAPILOC]![ILYQOT]-[MA#FILECEN_MAPILOC]![ILYQIN] AS [YTD Usage]
, [MA#FILECEN_MAPCSPL].CSZPFG

FROM ((([MA#FILECEN_MAPCITM] INNER JOIN [MA#FILECEN_MAPILOC]
ON [MA#FILECEN_MAPCITM].CTITEM = [MA#FILECEN_MAPILOC].ILITEM)
INNER JOIN [MA#FILECEN_MAPCSPL]
ON [MA#FILECEN_MAPILOC].ILHOSP = [MA#FILECEN_MAPCSPL].CSHOSP
AND [MA#FILECEN_MAPILOC].ILITEM = [MA#FILECEN_MAPCSPL].CSITEM )
INNER JOIN [MA#FILECEN_MAPVDIV]
ON [MA#FILECEN_MAPCSPL].CSVID = [MA#FILECEN_MAPVDIV].VXVID)

WHERE [MA#FILECEN_MAPILOC].ILHOSP=[Enter Hosp Number] AND
[MA#FILECEN_MAPILOC].ILLOCN=[Enter Location] AND
[MA#FILECEN_MAPILOC].ILUDAT<=DateAdd("d",[Enter Number of days],Date())
ORDER BY [MA#FILECEN_MAPILOC].ILUDAT DESC;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Karl Thanks for the response. I tried this and it isn't working either. I am
still getting an ODBC error. I have attached the SQL to maybe shed some
additional light on the subject. Thanks

SELECT [MA#FILECEN_MAPILOC].ILHOSP AS Hosp, [MA#FILECEN_MAPILOC].ILLOCN AS
Locn, [MA#FILECEN_MAPILOC].ILITEM AS [Item#], [MA#FILECEN_MAPCITM].CTDESC AS
[Desc], [MA#FILECEN_MAPCITM].[CTMAN#] AS [Manu#], [MA#FILECEN_MAPILOC].ILBIN1
AS Bin,
IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTBUNT],([MA#FILECEN_MAPILOC]![ILONHD]/1),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT2],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV2]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT3],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV3]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT4],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV4]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT5],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV5]),IIf([MA#FILECEN_MAPILOC]![ILDUNT]=[MA#FILECEN_MAPCITM]![CTUNT6],([MA#FILECEN_MAPILOC]![ILONHD]/[MA#FILECEN_MAPCITM]![CTCNV6])))))))
AS [On Hand Qty], [MA#FILECEN_MAPVDIV].VXDNAM AS [Vend Name],
[MA#FILECEN_MAPILOC].ILFLG2 AS [Value Class], [MA#FILECEN_MAPILOC].ILUDAT AS
[Last Used Date], [MA#FILECEN_MAPILOC]![ILYQOT]-[MA#FILECEN_MAPILOC]![ILYQIN]
AS [YTD Usage], [MA#FILECEN_MAPCSPL].CSZPFG
FROM (([MA#FILECEN_MAPCITM] INNER JOIN [MA#FILECEN_MAPILOC] ON
[MA#FILECEN_MAPCITM].CTITEM = [MA#FILECEN_MAPILOC].ILITEM) INNER JOIN
[MA#FILECEN_MAPCSPL] ON ([MA#FILECEN_MAPILOC].ILHOSP =
[MA#FILECEN_MAPCSPL].CSHOSP) AND ([MA#FILECEN_MAPILOC].ILITEM =
[MA#FILECEN_MAPCSPL].CSITEM)) INNER JOIN [MA#FILECEN_MAPVDIV] ON
[MA#FILECEN_MAPCSPL].CSVID = [MA#FILECEN_MAPVDIV].VXVID
WHERE ((([MA#FILECEN_MAPILOC].ILHOSP)=[Enter Hosp Number]) AND
(([MA#FILECEN_MAPILOC].ILLOCN)=[Enter Location]) AND
(([MA#FILECEN_MAPILOC].ILUDAT)<=Date()-CVDate([Enter number Of Days])))
ORDER BY [MA#FILECEN_MAPILOC].ILUDAT DESC;


KARL DEWEY said:
Try this --
<=Date()-CVDate([Enter number Of Days])

--
Build a little, test a little.


Dan said:
Hi I am having a problem in a query I am trying to pull. I have a date feild
that I am querying and in the criteria I have this.
<=Date()-[Enter number Of Days] when I run this it gives me an ODBC error.
If I changeit to this <=Date()-30 Than it works and pulls just everything 30
days or older. Is there anyway to make the parameter value work in this type
of scenario?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date parameter question 2
Date/Time Selection problem!!!!! 10
Date parameter 3
Export to Excel with Parameter in Filename 1
Parameter Queries in Crosstab Query 2
Parameter Query with DSum 1
Parameter Values prompted for twice 5
Parameter query with expressions and calculations 3

Top