Date and Parameter question.

  • Thread starter Thread starter Dan
  • Start date Start date
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?
 
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...
 
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...
 
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...
 
Back
Top