Results 1 to 4 of 4
  1. #1
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11

    Data type mismatch error on all of my queries!

    Hi all,



    I am having serious problems creating my queries. I keep getting the "Data type mismatch in expression" error whenever I try to add a new query.

    I have 5 tables...

    Booking - bookingcode, delegatecode, eventcode, bookingfee, bedandbreakfast, bedandbreakfastfee, presenting?

    Delegate - delegatecode, delegatename, delegateaddress, delegatephone

    Course - coursecode, coursedate, coursetitle, coursefee, courseduration

    Venue - venuecode, venueaddress, venuename, venuecost, venuephone

    Event - eventcode, venuecode, coursecode, eventname, eventdate, presenter

    My queries in SQL statements are as follows...

    Code:
    select coursetitle, eventdate, venuename
    from course, event, venue
    where course.coursecode = event.coursecode
    and venue.venuecode = event.venuecode;
    
    select venuename, count(bedandbreakfast) as totalbb
    from venue, booking, event
    where booking.eventcode = event.eventcode
    and event.venuecode = venue.venuecode
    and bedandbreakfast='Yes' group by venuename;
    
    select delegatename, presenting, bookingfee
    from booking, delegate
    where presenting = "No" 
    and booking.delegatecode = delegate.delegatecode;
    
    select delegatename, bedandbreakfast, bedandbreakfastfee from booking, delegate 
    where booking.delegatecode = delegate.delegatecode;
    
    select delegatename, bookingfee, presenting from delegate, booking 
    where booking.delegatecode = delegate.delegatecode;
    Thanks in advance for any help.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What is the datatype of all of your code fields (bookingcode, delegatecode, eventcode etc.)? If the primary key field (i.e. eventcode in the event table is autonumber, I assume), the corresponding event code in the related table such as the delegate table must be a long number datatype.

    Can you also explain your relationships between the tables?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Go to the Table Design view and see how each of the fields are defined.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Mark,

    I'd be interested to see your layout too. I remember your last couple of posts. I have never seen as many cross join queries being used as what you've got. Isn't there a simpler way to do the job in the db besides using cross joins, I wonder?

    It just really looks like your job is extremely difficult. I personally wouldn't even know what to do with all of that data you're probably getting in those outputs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch in criteria expression
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 11-23-2010, 10:46 AM
  2. Data type mismatch in query
    By thart21 in forum Queries
    Replies: 8
    Last Post: 10-01-2010, 01:40 PM
  3. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 PM
  4. Replies: 4
    Last Post: 10-26-2009, 05:27 AM
  5. Replies: 2
    Last Post: 03-23-2009, 05:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums