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?
 
Ad

Advertisements

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?
 
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?
 
Ad

Advertisements

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?
 

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

Top