Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2012
    Posts
    8

    Unanswered: Data Type Mismatch In Query Expression: NEED HELP!

    I have a table (Contact Tracker) that has a field (PostName) which is looking up the value from another table (MasterPost). I have created a Public Function PostName so my Form Dialog can run a Combo Box to look up the posts that i want to return the contact values for. When I run the query that i have created, it will not accept the Function PostName() in the criteria expression, and gives me the "type mismatch in expression" error. When I remove the Function, the query runs, however, returns all contacts for all posts, rather than just the individual post that i selected in my Form Dialog.

    A) I am not sure how to make my Function work in the query
    B) If my problem is due to field in the MasterPost table being a "text" field, and the field in Contact Tracker table being a "number" field due to being a "lookup", then I am not sure how to make my query work, or how to make the Combo Box on my Form Dialog pull the names of the posts rather then numbers.

    I've copied my Modual QC Functions code and the code for the cmdviewreport on the Form Dialog button. I AM NOT A PROGRAMMER, AND IN A LEARNING STAGE! Can anyone help me out / get me in the right direction here?

    MODUAL QC FUNTIONS:
    Option Compare Database
    Option Explicit

    Public varPostName As String

    Public Function PostName() As String
    PostName = varPostName
    End Function

    CMDVIEWREPORT:
    Option Compare Database
    Option Explicit

    Private Sub cmdViewReport_Click()
    On Error GoTo err_label

    varFromDate = Me.txtFromDate
    varToDate = Me.txtToDate
    varPostName = Me.cboPostName

    DoCmd.OpenReport "2012 QC PER INDIVIDUAL POST", acViewPreview

    err_exit:
    Exit Sub

    err_label:
    MsgBox (Err.Description)
    GoTo err_exit
    End Sub

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    I don't see why you need to use the function as long as the form with the combo on stays open when you open the report just reference it in the query. So, replace the function in the query with something like this:

    Forms!FORMNAME!COMBONAME

    NB: Just replace FORMNAME with the actual name of the form with the combo on it and replace COMBONAME with the actual name of the combobox.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Apr 2012
    Posts
    1

    Wink Mr


  4. #4
    Join Date
    Apr 2012
    Posts
    8

    Still Confused!

    Ok, where do I put in the Forms!FORMNAME!COMBONAME? If my field is "PostName", and the table is "Contact Tracker", do I put the following in the "Criteria" row below?

    [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName]"

    When I put the above in the criteria row, it still returned ALL POSTS LISTED IN THE TABLE.

    However, when I made a new field "Expr1: [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName]" and then ran the report, the Post Name that I selected from the combobox appeared on the report, but the report still pulled ALL Post Names that were entered into the table.

  5. #5
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    The line of code should go on the criteria row and overwrite PostName()
    If you can't get it to work try hardwriting in the value you want to use as a filter and then have a look in the datasheet view at the results to make sure you are seeing what you want.

    One other thing to check - maybe a silly question but - are the changes you're making to the query are definitely changing the recordset of the query?
    Regards
    JD

    Software-Matters

  6. #6
    Join Date
    Apr 2012
    Posts
    8

    Still Not Working

    I am at a loss here. When I have the criteria set to [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName] and then run the query, all results are blank. when I run the query with [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName] removed from the criteria, then I get results returned for all posts visited between the specified dates.

    Question: can my results be affected by the relationship between my tables

    Include ALL records from 'MASTER - POST SCHEDULES' and only those records from 'CONTACT TRACKER' where the joined fields are equal.

    I am running out of reasons that I can think of for why this is not working correctly.

  7. #7
    Join Date
    Apr 2012
    Posts
    8

    Still Not Working

    I am at a loss here. When I have the criteria set to [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName] and then run the query, all results are blank. when I run the query with [Forms]![LAUNCH DIALOG - QC PER POST]![cboPostName] removed from the criteria, then I get results returned for all posts visited between the specified dates.

    Question: can my results be affected by the relationship between my tables

    Include ALL records from 'MASTER - POST SCHEDULES' and only those records from 'CONTACT TRACKER' where the joined fields are equal.

    I am running out of reasons that I can think of for why this is not working correctly.

    I've attached a zip file of the database and all the components so that you can look at what I've got / done.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your problem is due to you having PostName as a lookup field to the other table. Your query is expecting the ID, not the name, as that is what is actually stored. This is one reason many of us don't like lookup fields:

    The Access Web - The Evils of Lookup Fields in Tables

    I'd get rid of it, but you can modify the combo to return the ID and the name, make sure ID is the bound column, and your query will work.
    Paul

  9. #9
    Join Date
    Apr 2012
    Posts
    8

    Thanks For The Response, But Further Confused

    I had attempted to get rid of the Lookup and when I did, as you said would happen all that was returned were number values identifying the PostNames, and my form that runs the report with the combobox was listing numbers instead of the PostName as well.

    My problem with getting rid of the Lookup is that my Contact Tracker Table already has almost 3000 records, and when i changed the value in the table from "Number" to "Text", all my records from the 1st of the year changed to numbers and no longer listed the post names, which left me with the issue of having to go back and somehow change all those previous entries.

    I'm interested in the idea of having my combobox lookup the ID and PostName, but am confused with how to do that, and don't understand how to make sure that the ID is the "bound" column. What would the code for the column or criteria look like? I apologize for being novice at this.

    ATTACHED IS THE DATABASE I AM WORKING ON IF YOU NEED TO REFERENCE WHAT I'VE GOT / DONE
    Attached Files Attached Files

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Normalized design would only store the number in related tables. You would use queries to display the name in reports and such. That's actually what the lookup field does, but it hides it from you, so you have problems like you had here.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •