Need to Create a Query

[rblivewire]

I am having trouble trying to figure out a criteria for a query. I
have 2 date columns, PDM and Web, and if both have dates in them then
the request is considered CLOSED. The query I need is for all items to
show up that have been closed in a user input date. Ex. If the user
inputs March 2004, all items that HAVE BEEN CLOSED IN MARCH 2004 need
to show up. This means that if PDM has a date in January 2004 and Web
has a date in March 2004, it should show up. If both have dates in
March, it should show up. If either one has a date after March, it
should not show up. If both have dates BEFORE March, it should NOT
show up. Basically, the one that has the latest date in column, that
date should be sometime in March. Any suggestions as to a criteria...
maybe an IF statement of some kind?

 

[Marshall Barton]

I am having trouble trying to figure out a criteria for a query. I
have 2 date columns, PDM and Web, and if both have dates in them then
the request is considered CLOSED. The query I need is for all items to
show up that have been closed in a user input date. Ex. If the user
inputs March 2004, all items that HAVE BEEN CLOSED IN MARCH 2004 need
to show up. This means that if PDM has a date in January 2004 and Web
has a date in March 2004, it should show up. If both have dates in
March, it should show up. If either one has a date after March, it
should not show up. If both have dates BEFORE March, it should NOT
show up. Basically, the one that has the latest date in column, that
date should be sometime in March.

I think this will do that:

WHERE IIf(PDM > Web, PDM, Web) Between
DateSerial(Year([Enter Date]), Month([Enter Date]), 1) And
DateSerial(Year([Enter Date]), Month([Enter Date]) + 1, 0)

 

[rblivewire]

Which column should I put this in... the PDM or the Web? or Both?

 

[Marshall Barton]

Which column should I put this in... the PDM or the Web? or Both?

Neither.

I was expecting you to use SQL View, but in the query design
grid place IIf(PDM > Web, PDM, Web) in the field row in a
new blank column. Then the Criteria for this calculated
field would be:

Between DateSerial(Year([Enter Date]), Month([Enter Date]),
1) And DateSerial(Year([Enter Date]), Month([Enter Date]) +
1, 0)

You probably want to uncheck the Show box too.

 

[rblivewire]

Thanks Marsh, but I tried it both in the SQL view and the query design
and my results keep coming up as nothing. Any other suggestions???

 

[Marshall Barton]

Thanks Marsh, but I tried it both in the SQL view and the query design
and my results keep coming up as nothing. Any other suggestions???

I think I posted the right thing. Let me see your SQL.

The only other thought I have is if your "date" columns are
really Text type fieds in the table, then it would need some
additional stuff.

 

[rblivewire]

Marsh,
With the beginning part of your statement: "IIf(PDM > Web, PDM, Web),"
when PDM has a date in the field and Web doesn't, the date ends up not
showing up in the field. If Web has a date in the field and PDM
doesn't, the date shows up. Any reason for this?

 

[Marshall Barton]

Marsh,
With the beginning part of your statement: "IIf(PDM > Web, PDM, Web),"
when PDM has a date in the field and Web doesn't, the date ends up not
showing up in the field. If Web has a date in the field and PDM
doesn't, the date shows up. Any reason for this?

Ahhh, I see what you're asking now. If either field is
Null, then the IIF condition fails (because Null can not be
compared to anything), so you get the value of Web (which
may have been the reason for condition to fail in the first
place). Well that sounds like so much gobbledygook, let's
just try changing it to:

IIf(Nz(PDM, 0) > Nz(Web, 0), PDM, Web)

 

[rblivewire]

That's it Marsh... thanks a lot for the help... it is greatly
appreciated