Results 1 to 2 of 2
  1. #1
    benlw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    1

    Criteria expression

    Hi there,



    I'm relatively new to MS Access and I'm having what I think is more than likely a bit of a basic problem.

    I've got a large table containing staff data with a date field of when the employee commenced. I've got a date figure (which is updated annually) housed in a separate table (I'll refer to it as 'the date table').

    I'm trying to build a query that will provide a count of the number of employees that fall within certain employee types that have commenced on or after the date stored in the date table. The query I've currently got selects the number of employees that fall within my target employee types fine. The problem I have is when I introduce the date commenced field and add the criteria for the date having to be on or after the date stored in the date table. The criteria expression is currently '>=[tbl_Report_Dates]![intake date]'

    The problem I have is that when I run the query it pops up a 'Enter parameter value' messagebox for the tbl_Report_Dates!intake date field.

    I know this is probably something quite simple but any assistance would be most appreciated.

    Cheers

    Ben

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Code:
    SELECT Count(a.[whateverfieldyouwant]) as [ANameifyouwant]
    FROM StaffTbl as a
    INNER JOIN [the date table] as b
    ON a.StaffID = b.StaffID
    WHERE b.CommenceDate <= '01/07/2010' or Textbox1.Text
    If above is the kind of thing you are doing, and it is correct (I.e it works without the date part) then it *must* be either the date field is not in the correct format.
    Or the textbox/field in which the person enters data doesn't correctly convert the date across.

    Not sure, but do you have to set the format of the textbox/report/field in which you are inputting data? (allowing a certain type? like nvarchar/datetime etc)

    These are my guesses, I might be wrong so hopefully other people will suggest ideas if i'm incorrect.

    Good luck.

    Rixxe.

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 criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 AM
  3. Data type mismatch in criteria expression
    By shexe in forum Queries
    Replies: 2
    Last Post: 09-01-2010, 12:47 PM
  4. Replies: 1
    Last Post: 03-23-2010, 04:01 PM
  5. Help With an expression
    By kylem4711 in forum Queries
    Replies: 2
    Last Post: 04-23-2009, 01:57 PM

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