extract date then search by the date

[dnr]

I appreciate any help with this, I am fairly new to writting programs etc.
In a database, I track data by a unique product lot number and the lot number
has the date imbedded in it, lets call the field name lot. An example lot
number is Z090112422 the format being Zyymmdd422. In a query called
QualityData in one field I have written:

date: CVdate(Mid([lot],4,2) & "/" & Mid([lot],6,2) & "/" & Mid([lot],2,2))

The query contains other fields - but this is the only one with any
expression. This expression displays above example as 1/12/2009.

I then create another query with the query QualityData as the source, I add
all of the fields but in the date field I have criteria to allow the user to
narrow the date range they are looking for. I have tried writting the
criteria many different ways but it does not narrow the values - it still
displays all the data. Here are some of the ways I have tried:

Between [#start#] And [#finish#] ( I enter 08/18/08 for start, and 01/09/09
for finish)

Between [start] And [finish] ( I enter 08/18/08 for start, and 01/09/09 for
finish)

">08/18/09"

Nothing has worked - please help not sure what to do...
Thanks
DNR

 

[John W. Vinson]

I appreciate any help with this, I am fairly new to writting programs etc.
In a database, I track data by a unique product lot number and the lot number
has the date imbedded in it, lets call the field name lot. An example lot
number is Z090112422 the format being Zyymmdd422. In a query called
QualityData in one field I have written:

Well... it's generally A Bad Idea to encode multiple data types and data items
into a field. This is a good example of the reason why you shouldn't. Dates
*ARE DATA*, and ideally should be stored seperately, as a date field.

date: CVdate(Mid([lot],4,2) & "/" & Mid([lot],6,2) & "/" & Mid([lot],2,2))

Alternatively,

LotDate: DateSerial(Mid([Lot], 2, 2), Mid([Lot], 4, 2), Mid([Lot], 6, 2))

Note that Date is a reserved word and a bad choice for a fieldname.

The query contains other fields - but this is the only one with any
expression. This expression displays above example as 1/12/2009.

I then create another query with the query QualityData as the source, I add
all of the fields but in the date field I have criteria to allow the user to
narrow the date range they are looking for. I have tried writting the
criteria many different ways but it does not narrow the values - it still
displays all the data. Here are some of the ways I have tried:

Between [#start#] And [#finish#] ( I enter 08/18/08 for start, and 01/09/09
for finish)

Between [start] And [finish] ( I enter 08/18/08 for start, and 01/09/09 for
finish)

">08/18/09"

Nothing has worked - please help not sure what to do...

Are you putting the criterion on Lot, or on the calculated field [date]? Is
the Z in the first character of Lot a constant, or a variable?

As a criterion on the calculated field [date] you should be able to use

Parameters [start] Date/Time, [finish] Date/Time;
SELECT...
....
WHERE [date] >= [start] AND [date] <= [finish]
....;

Definining the paramters will ensure that Access treats them as date/time
values.

 

[dnr]

John,

Thanks for the help - but I am still having problems. I changed the way I
retrieve the date to the DateSerial as you suggested and have changed the
name of the field to BLotDate - this appears to be working well. I then
changed the query as you suggested and it won't solve it - I get an error
that says the expression is typed incorrectly or it is to complex to solve.
Here is my query below any suggestions??


PARAMETERS START DateTime, [END] DateTime;
SELECT CrateQC.[CLotNumber], CrateQC.[BLotNumber], CrateQC.[Average],
CrateQC.[Average], CrateQC.[SG], CrateQC.Hard, CrateQC.Thick,
CrateQC.[Numeric ID], CrateQC.BLotDate
FROM CrateQC
WHERE (((CrateQC.BLotDate) Between [START] And [END]));

I appreciate any help with this, I am fairly new to writting programs etc.
In a database, I track data by a unique product lot number and the lot number
has the date imbedded in it, lets call the field name lot. An example lot
number is Z090112422 the format being Zyymmdd422. In a query called
QualityData in one field I have written:

Well... it's generally A Bad Idea to encode multiple data types and data items
into a field. This is a good example of the reason why you shouldn't. Dates
*ARE DATA*, and ideally should be stored seperately, as a date field.

date: CVdate(Mid([lot],4,2) & "/" & Mid([lot],6,2) & "/" & Mid([lot],2,2))

Alternatively,

LotDate: DateSerial(Mid([Lot], 2, 2), Mid([Lot], 4, 2), Mid([Lot], 6, 2))

Note that Date is a reserved word and a bad choice for a fieldname.

The query contains other fields - but this is the only one with any
expression. This expression displays above example as 1/12/2009.

I then create another query with the query QualityData as the source, I add
all of the fields but in the date field I have criteria to allow the user to
narrow the date range they are looking for. I have tried writting the
criteria many different ways but it does not narrow the values - it still
displays all the data. Here are some of the ways I have tried:

Between [#start#] And [#finish#] ( I enter 08/18/08 for start, and 01/09/09
for finish)

Between [start] And [finish] ( I enter 08/18/08 for start, and 01/09/09 for
finish)

">08/18/09"

Nothing has worked - please help not sure what to do...

Are you putting the criterion on Lot, or on the calculated field [date]? Is
the Z in the first character of Lot a constant, or a variable?

As a criterion on the calculated field [date] you should be able to use

Parameters [start] Date/Time, [finish] Date/Time;
SELECT...
....
WHERE [date] >= [start] AND [date] <= [finish]
....;

Definining the paramters will ensure that Access treats them as date/time
values.

 

[John W. Vinson]

John,

Thanks for the help - but I am still having problems. I changed the way I
retrieve the date to the DateSerial as you suggested and have changed the
name of the field to BLotDate - this appears to be working well. I then
changed the query as you suggested and it won't solve it - I get an error
that says the expression is typed incorrectly or it is to complex to solve.
Here is my query below any suggestions??


PARAMETERS START DateTime, [END] DateTime;
SELECT CrateQC.[CLotNumber], CrateQC.[BLotNumber], CrateQC.[Average],
CrateQC.[Average], CrateQC.[SG], CrateQC.Hard, CrateQC.Thick,
CrateQC.[Numeric ID], CrateQC.BLotDate
FROM CrateQC
WHERE (((CrateQC.BLotDate) Between [START] And [END]));

The Parameters declaration and the WHERE condition use of those parameters
must match *exactly* - try putting START in square brackets in the PARAMETERS
clause. What specifically are you entering in response to the START and END
prompts?

 

[dnr]

John,

I did change the START to [START] in the parameters. It did not make a
difference, I still get the following error message:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

When prompted for the parameters, I have tried 08/18/08 and 08/18/2008 for
the start and have tried 01/09/09 and 01/09/2009 for end parameter. Not sure
what to do.

John,

Thanks for the help - but I am still having problems. I changed the way I
retrieve the date to the DateSerial as you suggested and have changed the
name of the field to BLotDate - this appears to be working well. I then
changed the query as you suggested and it won't solve it - I get an error
that says the expression is typed incorrectly or it is to complex to solve.
Here is my query below any suggestions??


PARAMETERS START DateTime, [END] DateTime;
SELECT CrateQC.[CLotNumber], CrateQC.[BLotNumber], CrateQC.[Average],
CrateQC.[Average], CrateQC.[SG], CrateQC.Hard, CrateQC.Thick,
CrateQC.[Numeric ID], CrateQC.BLotDate
FROM CrateQC
WHERE (((CrateQC.BLotDate) Between [START] And [END]));

The Parameters declaration and the WHERE condition use of those parameters
must match *exactly* - try putting START in square brackets in the PARAMETERS
clause. What specifically are you entering in response to the START and END
prompts?

 

[John Spencer (MVP)]

I suspect that you are having a problem in the CrateQC query and that it is
generating an error when trying construct the date.

You said you were using the expression:
CVdate(Mid([lot],4,2) & "/" & Mid([lot],6,2) & "/" & Mid([lot],2,2))
Mid(Lot,4,

You might try this expression which checks that the resulting string can be
converted to a date and if it can it does the conversion. If not, it returns
null.

IIF(IsDate(Format(Mid(Lot,4,4) & Mid(Lot,2,2),"@@/@@/@@"))
,CDate(Format(Mid(Lot,4,4) & Mid(Lot,2,2),"@@/@@/@@")) ,Null)

CVDate will error on a malformed date string - for instance a zero-length
string will generate a type 13 error.

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

John,

I did change the START to [START] in the parameters. It did not make a
difference, I still get the following error message:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

When prompted for the parameters, I have tried 08/18/08 and 08/18/2008 for
the start and have tried 01/09/09 and 01/09/2009 for end parameter. Not sure
what to do.

John,

Thanks for the help - but I am still having problems. I changed the way I
retrieve the date to the DateSerial as you suggested and have changed the
name of the field to BLotDate - this appears to be working well. I then
changed the query as you suggested and it won't solve it - I get an error
that says the expression is typed incorrectly or it is to complex to solve.
Here is my query below any suggestions??


PARAMETERS START DateTime, [END] DateTime;
SELECT CrateQC.[CLotNumber], CrateQC.[BLotNumber], CrateQC.[Average],
CrateQC.[Average], CrateQC.[SG], CrateQC.Hard, CrateQC.Thick,
CrateQC.[Numeric ID], CrateQC.BLotDate
FROM CrateQC
WHERE (((CrateQC.BLotDate) Between [START] And [END]));

The Parameters declaration and the WHERE condition use of those parameters
must match *exactly* - try putting START in square brackets in the PARAMETERS
clause. What specifically are you entering in response to the START and END
prompts?

 

[dnr]

John,

That took care of the error I was getting - Thank you so much!!

I suspect that you are having a problem in the CrateQC query and that it is
generating an error when trying construct the date.

You said you were using the expression:
CVdate(Mid([lot],4,2) & "/" & Mid([lot],6,2) & "/" & Mid([lot],2,2))
Mid(Lot,4,

You might try this expression which checks that the resulting string can be
converted to a date and if it can it does the conversion. If not, it returns
null.

IIF(IsDate(Format(Mid(Lot,4,4) & Mid(Lot,2,2),"@@/@@/@@"))
,CDate(Format(Mid(Lot,4,4) & Mid(Lot,2,2),"@@/@@/@@")) ,Null)

CVDate will error on a malformed date string - for instance a zero-length
string will generate a type 13 error.

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

John,

I did change the START to [START] in the parameters. It did not make a
difference, I still get the following error message:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

When prompted for the parameters, I have tried 08/18/08 and 08/18/2008 for
the start and have tried 01/09/09 and 01/09/2009 for end parameter. Not sure
what to do.

John,

Thanks for the help - but I am still having problems. I changed the way I
retrieve the date to the DateSerial as you suggested and have changed the
name of the field to BLotDate - this appears to be working well. I then
changed the query as you suggested and it won't solve it - I get an error
that says the expression is typed incorrectly or it is to complex to solve.
Here is my query below any suggestions??


PARAMETERS START DateTime, [END] DateTime;
SELECT CrateQC.[CLotNumber], CrateQC.[BLotNumber], CrateQC.[Average],
CrateQC.[Average], CrateQC.[SG], CrateQC.Hard, CrateQC.Thick,
CrateQC.[Numeric ID], CrateQC.BLotDate
FROM CrateQC
WHERE (((CrateQC.BLotDate) Between [START] And [END]));
The Parameters declaration and the WHERE condition use of those parameters
must match *exactly* - try putting START in square brackets in the PARAMETERS
clause. What specifically are you entering in response to the START and END
prompts?