Date Criteria not working...

  • Thread starter Thread starter MarieG
  • Start date Start date
M

MarieG

Hello all...

I am trying to query a few tables, but in one of the criteria I use >[non
hold date]. When I do that, Access never pulls the data and I have to shut
it down and restart. However when I replace >[non hold date] with
#06/12/09#, it works fine. I need it to work the first way though because
Click to expand...
I'll be makine 17 of the queries for one big table. Any ideas?

Sorry so long, but thanks guys!!

MGreenly

SELECT "OGA" AS Client, [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_ActivityLog-TMC002-OGA].LastModifiedBy,
[dbo_ActivityLog-TMC002-OGA].FunctionName,
[dbo_ActivityLog-TMC002-OGA].TableName,
[dbo_vBilling_Visits-TMC002-OGA].Patient,
[dbo_ActivityLog-TMC002-OGA].Value1, [dbo_ActivityLog-TMC002-OGA].Value2,
[dbo_MedLists-TMC002-OGA].Description AS [Bill Status Description],
[dbo_vBilling_Visits-TMC002-OGA].Description AS [Billing Visit Description],
[dbo_vBilling_Visits-TMC002-OGA].ApprovalResults,
[dbo_vBilling_Visits-TMC002-OGA].CurrentInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].CurrentCarrier,
[dbo_vBilling_Visits-TMC002-OGA].PrimaryInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].LastFiledDate,
[dbo_vBilling_Visits-TMC002-OGA].FilingMethod,
[dbo_vBilling_Visits-TMC002-OGA].vBalance,
[dbo_vBilling_Visits-TMC002-OGA].vInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].vPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].pBalance,
[dbo_vBilling_Visits-TMC002-OGA].pInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].pPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].ClaimStatus,
[dbo_ActivityLog-TMC002-OGA].LastModified,
[dbo_ActivityLog-TMC002-OGA].RecordId INTO [HOLDLIST TABLE]
FROM [Date Range], [dbo_ActivityLog-TMC002-OGA] INNER JOIN
([dbo_MedLists-TMC002-OGA] INNER JOIN [dbo_vBilling_Visits-TMC002-OGA] ON
[dbo_MedLists-TMC002-OGA].JoinId =
[dbo_vBilling_Visits-TMC002-OGA].BillStatus) ON
[dbo_ActivityLog-TMC002-OGA].RecordId =
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID
WHERE ((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from*") AND (([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description)="hold") AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus")) OR
((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from *" And ([dbo_ActivityLog-TMC002-OGA].Value1) Not Like "from ") AND
(([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description) Not Like "hold") AND
(([dbo_ActivityLog-TMC002-OGA].LastModified)>[non hold date]) AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus"))
ORDER BY [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService;
 
You did not say what you are getting that should not be there or what is
missing that should. It would be helpful in solving the problem. Now, there
is one common error when doing date comparison. It has to do with whether
there is a time component to the value.

For example, if you use the Date() function, it will return something like:

6/30/2009

If you use the Now() function, it wll return something like:

6/30/2009 9:21:30 AM

Well, that's not exactly what it returns, what you see is a formatted
representation of the date/time value. It is actually a floating point
decimal number where the whole numbe represents the number of day from (I
think, but don't remember for sure) 12, 31, 1899 and the decimal value
represents the number of seconds since midnight. So at midnight, the decimal
value is 0.

So, if the field in the table contains a time component and the value you
are comparing it to does not have a time component, some records you think
should be included are not. Here is an example:

Assume the field contains a time component.
The comparison value is BETWEEN #1/1/2009# AND #1/7/2009#
So that would actually be BETWEEN #1/1/2009 00:00:00 AM# AND #1/1/2009
00:00:00 AM#

When we evaluate a record and the field contains #1/1/2009 08:23:41 AM#, it
will not be include in the recordset because it is greater than the compare
value.
--
Dave Hargis, Microsoft Access MVP


MarieG said:
Hello all...

I am trying to query a few tables, but in one of the criteria I use >[non
hold date]. When I do that, Access never pulls the data and I have to shut
it down and restart. However when I replace >[non hold date] with
#06/12/09#, it works fine. I need it to work the first way though because
Click to expand...
I'll be makine 17 of the queries for one big table. Any ideas?

Sorry so long, but thanks guys!!

MGreenly

SELECT "OGA" AS Client, [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_ActivityLog-TMC002-OGA].LastModifiedBy,
[dbo_ActivityLog-TMC002-OGA].FunctionName,
[dbo_ActivityLog-TMC002-OGA].TableName,
[dbo_vBilling_Visits-TMC002-OGA].Patient,
[dbo_ActivityLog-TMC002-OGA].Value1, [dbo_ActivityLog-TMC002-OGA].Value2,
[dbo_MedLists-TMC002-OGA].Description AS [Bill Status Description],
[dbo_vBilling_Visits-TMC002-OGA].Description AS [Billing Visit Description],
[dbo_vBilling_Visits-TMC002-OGA].ApprovalResults,
[dbo_vBilling_Visits-TMC002-OGA].CurrentInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].CurrentCarrier,
[dbo_vBilling_Visits-TMC002-OGA].PrimaryInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].LastFiledDate,
[dbo_vBilling_Visits-TMC002-OGA].FilingMethod,
[dbo_vBilling_Visits-TMC002-OGA].vBalance,
[dbo_vBilling_Visits-TMC002-OGA].vInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].vPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].pBalance,
[dbo_vBilling_Visits-TMC002-OGA].pInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].pPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].ClaimStatus,
[dbo_ActivityLog-TMC002-OGA].LastModified,
[dbo_ActivityLog-TMC002-OGA].RecordId INTO [HOLDLIST TABLE]
FROM [Date Range], [dbo_ActivityLog-TMC002-OGA] INNER JOIN
([dbo_MedLists-TMC002-OGA] INNER JOIN [dbo_vBilling_Visits-TMC002-OGA] ON
[dbo_MedLists-TMC002-OGA].JoinId =
[dbo_vBilling_Visits-TMC002-OGA].BillStatus) ON
[dbo_ActivityLog-TMC002-OGA].RecordId =
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID
WHERE ((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from*") AND (([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description)="hold") AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus")) OR
((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from *" And ([dbo_ActivityLog-TMC002-OGA].Value1) Not Like "from ") AND
(([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description) Not Like "hold") AND
(([dbo_ActivityLog-TMC002-OGA].LastModified)>[non hold date]) AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus"))
ORDER BY [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService;
Click to expand...
 
Assumption: [non hold date]) is a parameter and you get prompted to enter a date.

Try specifying the parameter type by adding the following to the BEGINNING of
your query

Parameters [non hold date] DateTime;
SELECT ...

OR change the where criteria to force the parameter to a date type.
dbo_ActivityLog-TMC002-OGA].LastModified > CDate([non hold date])

The problem could be that when you enter
06/12/09
in response to the prompt, the entry gets interpreted as a division statement.
So the result of that would be roughly 0.0556. That is equivalent to
12/30/1899 01:20:04.

Although in with your criteria of greater than it should still work, however
it will return every record where the LastModified date is greater than
December 31, 1899 (every record in your database probably has a LastModified
date greater than that date).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello all...

I am trying to query a few tables, but in one of the criteria I use >[non
hold date]. When I do that, Access never pulls the data and I have to shut
it down and restart. However when I replace >[non hold date] with
#06/12/09#, it works fine. I need it to work the first way though because
Click to expand...
I'll be makine 17 of the queries for one big table. Any ideas?

Sorry so long, but thanks guys!!

MGreenly

SELECT "OGA" AS Client, [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_ActivityLog-TMC002-OGA].LastModifiedBy,
[dbo_ActivityLog-TMC002-OGA].FunctionName,
[dbo_ActivityLog-TMC002-OGA].TableName,
[dbo_vBilling_Visits-TMC002-OGA].Patient,
[dbo_ActivityLog-TMC002-OGA].Value1, [dbo_ActivityLog-TMC002-OGA].Value2,
[dbo_MedLists-TMC002-OGA].Description AS [Bill Status Description],
[dbo_vBilling_Visits-TMC002-OGA].Description AS [Billing Visit Description],
[dbo_vBilling_Visits-TMC002-OGA].ApprovalResults,
[dbo_vBilling_Visits-TMC002-OGA].CurrentInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].CurrentCarrier,
[dbo_vBilling_Visits-TMC002-OGA].PrimaryInsuranceCarrier,
[dbo_vBilling_Visits-TMC002-OGA].LastFiledDate,
[dbo_vBilling_Visits-TMC002-OGA].FilingMethod,
[dbo_vBilling_Visits-TMC002-OGA].vBalance,
[dbo_vBilling_Visits-TMC002-OGA].vInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].vPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].pBalance,
[dbo_vBilling_Visits-TMC002-OGA].pInsBalance,
[dbo_vBilling_Visits-TMC002-OGA].pPatBalance,
[dbo_vBilling_Visits-TMC002-OGA].ClaimStatus,
[dbo_ActivityLog-TMC002-OGA].LastModified,
[dbo_ActivityLog-TMC002-OGA].RecordId INTO [HOLDLIST TABLE]
FROM [Date Range], [dbo_ActivityLog-TMC002-OGA] INNER JOIN
([dbo_MedLists-TMC002-OGA] INNER JOIN [dbo_vBilling_Visits-TMC002-OGA] ON
[dbo_MedLists-TMC002-OGA].JoinId =
[dbo_vBilling_Visits-TMC002-OGA].BillStatus) ON
[dbo_ActivityLog-TMC002-OGA].RecordId =
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID
WHERE ((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from*") AND (([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description)="hold") AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus")) OR
((([dbo_ActivityLog-TMC002-OGA].FunctionName) Not Like
"CParadigmUtil::PrintStatements") AND (([dbo_ActivityLog-TMC002-OGA].Value1)
Like "from *" And ([dbo_ActivityLog-TMC002-OGA].Value1) Not Like "from ") AND
(([dbo_ActivityLog-TMC002-OGA].Value2) Like "To*") AND
(([dbo_MedLists-TMC002-OGA].Description) Not Like "hold") AND
(([dbo_ActivityLog-TMC002-OGA].LastModified)>[non hold date]) AND
(([dbo_MedLists-TMC002-OGA].TableName)="billstatus"))
ORDER BY [dbo_ActivityLog-TMC002-OGA].PatientProfileId,
[dbo_vBilling_Visits-TMC002-OGA].TicketNumber,
[dbo_vBilling_Visits-TMC002-OGA].DateOfService;
Click to expand...
 
Back
Top