ms access data type mismatch query dates

Posted on 2011-05-03
Last Modified: 2012-05-11
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
Question by:johnmadigan
    LVL 51

    Accepted Solution

    your expression will give this


    when [TransactionDate] Is Null

    is that a valid date?
    LVL 51

    Expert Comment

    probably you need to change "1", the second parameter in iif to something else that gives you a date...
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    cdate(1) gives you a date: 12/31/1899
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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))))

    Author Closing Comment

    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.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now