[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

ms access data type mismatch query dates

I have a table with dates (WeekOf) for each monday in 2011.  I have a query called "qryPOTransReqDate".  In this query I have an expression to calculate a monday date depending on another date in the record. Here is the expression:

WeekOf: CDate(IIf([TransactionDate] Is Null,1,DateAdd("d",2-Weekday([TransactionDate]+[PaymentTermsDays]),[TransactionDate]+[PaymentTermsDays])))

When I try to join this query with the Table on the WeekOf field I get the mismatch.

It looks like a real date filed with I run the qryPOTransReqDate query so I don't know why it will not connect.

Any suggestions?


Thanks for your help
0
johnmadigan
Asked:
johnmadigan
  • 2
  • 2
2 Solutions
 
HainKurtSr. System AnalystCommented:
your expression will give this

CDate(1)

when [TransactionDate] Is Null

is that a valid date?
0
 
HainKurtSr. System AnalystCommented:
probably you need to change "1", the second parameter in iif to something else that gives you a date...
0
 
Dale FyeCommented:
cdate(1) gives you a date: 12/31/1899
0
 
Dale FyeCommented:
Have you tried

WeekOf: CDate(IIf(IsNull([TransactionDate]),1,DateAdd("d",2-Weekday([TransactionDate]+[PaymentTermsDays]),[TransactionDate]+[PaymentTermsDays])))

Any chance your field in your table displays a date, but is a text field?

I just created a table and put some dates in it.  I then created a query that uses syntax identical to yours (and above) and they both work.

Is there any chance that [PaymentTermsDays] is NULL?  If so, you might want to wrap the references to [PaymentTermsDays] in the NZ function and provide a default value, something like:

WeekOf: CDate(IIf(IsNull([TransactionDate]),1,DateAdd("d",2-Weekday([TransactionDate]+NZ([PaymentTermsDays], 7)),[TransactionDate]+NZ([PaymentTermsDays], 7))))
0
 
johnmadiganAuthor Commented:
I found out that the field that had the payment days had some null values.  I was getting errors it the query for these records.  

I corrected this by makiing the default payment days to 30 -

Thanks for your help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now