DateSerial not working

G

Guest

My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as 2/1/179.

What am I doing wrong?

Thanks for your help!
 
K

Ken Snell \(MVP\)

Don't use Format as an intermediate step:

DateSerial(Year([ProdDate]),Month([ProdDate],1)
 
G

Guest

Hi, Ken.

Unfortunately, I have to format it. This particular question is on the heels
of what I was working on in an earlier post:

To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This query returns [ProdDate1] in chronological order. No problems. It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1]);

In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.

--
www.Marzetti.com


Ken Snell (MVP) said:
Don't use Format as an intermediate step:

DateSerial(Year([ProdDate]),Month([ProdDate],1)

--

Ken Snell
<MS ACCESS MVP>


JohnLute said:
My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as 2/1/179.

What am I doing wrong?

Thanks for your help!
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

To avoid having to go through all kinds of "hoops" in order to try to change
your ProdDate1 field back to a chronologically sortable date, just add
ProdDate field to your first query:

SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M, [ProdDate]
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));


Then add it to your second query in the ORDER BY clause but with a "twist"
(see GROUP BY clause too):

SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1]),
Format([ProdDate], "yyyymm")
ORDER BY Format([ProdDate], "yyyymm");

--

Ken Snell
<MS ACCESS MVP>



JohnLute said:
Hi, Ken.

Unfortunately, I have to format it. This particular question is on the
heels
of what I was working on in an earlier post:

To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This query returns [ProdDate1] in chronological order. No problems. It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1]);

In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.

--
www.Marzetti.com


Ken Snell (MVP) said:
Don't use Format as an intermediate step:

DateSerial(Year([ProdDate]),Month([ProdDate],1)

--

Ken Snell
<MS ACCESS MVP>


JohnLute said:
My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as
2/1/179.

What am I doing wrong?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The DateSerial function expects its parameters in year, month, day order.
You're passing month, year, day.
 
G

Guest

Thanks a bunch, Ken.

Something's still not right. Maybe I'm misunderstanding and/or wasn't
complete in my details. I think I may have a problem with the 3rd query:
qryCompsFacsPerMonthSum

SELECT qryCompsFacsMonths.ProductionFacility,
(Format([DateReceived],"mmyyyy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1,
qryCompsFacsMonths.DateReceived
FROM qryCompsFacsMonths
GROUP BY qryCompsFacsMonths.ProductionFacility,
(Format([DateReceived],"mmyyyy")), qryCompsFacsMonths.DateReceived;

[DateReceived1] is formatted differently than
[qryFacsCasesProduced100M].[ProdDate1]

When I run qryCompsFacsPerMonthSum100M it returns null.

Is the format difference the problem?

--
www.Marzetti.com


Ken Snell (MVP) said:
To avoid having to go through all kinds of "hoops" in order to try to change
your ProdDate1 field back to a chronologically sortable date, just add
ProdDate field to your first query:

SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M, [ProdDate]
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));


Then add it to your second query in the ORDER BY clause but with a "twist"
(see GROUP BY clause too):

SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1]),
Format([ProdDate], "yyyymm")
ORDER BY Format([ProdDate], "yyyymm");

--

Ken Snell
<MS ACCESS MVP>



JohnLute said:
Hi, Ken.

Unfortunately, I have to format it. This particular question is on the
heels
of what I was working on in an earlier post:

To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This query returns [ProdDate1] in chronological order. No problems. It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1]);

In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.

--
www.Marzetti.com


Ken Snell (MVP) said:
Don't use Format as an intermediate step:

DateSerial(Year([ProdDate]),Month([ProdDate],1)

--

Ken Snell
<MS ACCESS MVP>


My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as
2/1/179.

What am I doing wrong?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks, Douglas!
--
www.Marzetti.com


Douglas J. Steele said:
The DateSerial function expects its parameters in year, month, day order.
You're passing month, year, day.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JohnLute said:
My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as 2/1/179.

What am I doing wrong?

Thanks for your help!
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

I didn't see a third query in your post? But, I believe that Doug Steele has
pointed you to a solution.

--

Ken Snell
<MS ACCESS MVP>


JohnLute said:
Thanks a bunch, Ken.

Something's still not right. Maybe I'm misunderstanding and/or wasn't
complete in my details. I think I may have a problem with the 3rd query:
qryCompsFacsPerMonthSum

SELECT qryCompsFacsMonths.ProductionFacility,
(Format([DateReceived],"mmyyyy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1,
qryCompsFacsMonths.DateReceived
FROM qryCompsFacsMonths
GROUP BY qryCompsFacsMonths.ProductionFacility,
(Format([DateReceived],"mmyyyy")), qryCompsFacsMonths.DateReceived;

[DateReceived1] is formatted differently than
[qryFacsCasesProduced100M].[ProdDate1]

When I run qryCompsFacsPerMonthSum100M it returns null.

Is the format difference the problem?

--
www.Marzetti.com


Ken Snell (MVP) said:
To avoid having to go through all kinds of "hoops" in order to try to
change
your ProdDate1 field back to a chronologically sortable date, just add
ProdDate field to your first query:

SELECT tblFacilitiesCasesProduced.txtFacilityID,
(Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M, [ProdDate]
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));


Then add it to your second query in the ORDER BY clause but with a
"twist"
(see GROUP BY clause too):

SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 =
qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1]),
Format([ProdDate], "yyyymm")
ORDER BY Format([ProdDate], "yyyymm");

--

Ken Snell
<MS ACCESS MVP>



JohnLute said:
Hi, Ken.

Unfortunately, I have to format it. This particular question is on the
heels
of what I was working on in an earlier post:

To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID,
(Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This query returns [ProdDate1] in chronological order. No problems.
It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 =
qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1]);

In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.

--
www.Marzetti.com


:

Don't use Format as an intermediate step:

DateSerial(Year([ProdDate]),Month([ProdDate],1)

--

Ken Snell
<MS ACCESS MVP>


My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))

I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)

This isn't working. For example, the value 122006 is returning as
2/1/179.

What am I doing wrong?

Thanks for your help!
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

calculated field rounding down 2
calculation rounding down 2
Problem with DateSerial 4
DateSerial Problem 5
DateSerial and Criteria Limits 13
Invalid use of null 3
Cross Tab Query with DateSerial 1
DateAdd against textbox value 4

Top