Enter Parameter Value Error (1 Viewer)

[NSAMSA]

All:

I created an access database almost 2 years ago that was functioning fine. A key query started giving me an Enter Parameter Value error a couple of months ago without me changing anything. If I make other queries around the same tables, they work but if I duplicate this query, it does not even when I build the whole thing again from scratch. I cannot figure out where the error might lie. Can someone please look at the below SQL and see if anything jumps out as prompting the error?

SELECT [04-RecordEntryMaster].[EE Number] AS [EE Number], [04-RecordEntryMaster].[EE Name], [04-RecordEntryMaster].[Course Name], [02-tblStaff].[Department Cost Center], [10-Department].Department, [04-RecordEntryMaster].Frequency, [04-RecordEntryMaster].[Last Date], [04-RecordEntryMaster].[Next Date], [04-RecordEntryMaster].Pass, [04-RecordEntryMaster].Status, [07-ScheduleMaster].ScheduleMonth, [07-ScheduleMaster].Type, [02-tblStaff].[Schedule Group], [07-ScheduleMaster].ScheduleYear, [09-tblShift].Shift, Left([04-RecordEntryMaster]![Course Name],20) AS [SOP No], Right([04-RecordEntryMaster]![Course Name],Len([04-RecordEntryMaster]![Course Name])-20) AS Description
FROM [07-ScheduleMaster] INNER JOIN ([10-Department] INNER JOIN ([04-RecordEntryMaster] INNER JOIN ([09-tblShift] INNER JOIN [02-tblStaff] ON [09-tblShift].[Schedule Group] = [02-tblStaff].[Schedule Group]) ON [04-RecordEntryMaster].[EE Number] = [02-tblStaff].[EE Number]) ON [10-Department].[Job Code Description] = [02-tblStaff].[Job Code Description]) ON [07-ScheduleMaster].ScheduleCourse.Value = [04-RecordEntryMaster].[Course Name]
WHERE ((([04-RecordEntryMaster].[EE Name]) Like "*" & [Forms]![frmRecordEntry]![cboName] & "*") AND (([10-Department].Department) Like "*" & [Forms]![frmRecordEntry]![cboDepartment] & "*") AND (([07-ScheduleMaster].ScheduleMonth) Like "*" & [Forms]![frmRecordEntry]![cboMonth] & "*") AND (([07-ScheduleMaster].Type)="Training") AND (([07-ScheduleMaster].ScheduleYear) Like "*" & [Forms]![frmRecordEntry]![cboYear] & "*") AND (([09-tblShift].Shift) Like "*" & [Forms]![frmRecordEntry]![cboShift] & "*"));


The error is:

Enter Parameter Value
07-ScheduleMaster.ScheduleCourse.Value

Thank you!

 

[jdraw]

SQL from post #1 via PoorSql.com for readability.

SELECT [04-RecordEntryMaster].[EE Number] AS [EE Number]
    , [04-RecordEntryMaster].[EE Name]
    , [04-RecordEntryMaster].[Course Name]
    , [02-tblStaff].[Department Cost Center]
    , [10-Department].Department
    , [04-RecordEntryMaster].Frequency
    , [04-RecordEntryMaster].[Last Date]
    , [04-RecordEntryMaster].[Next Date]
    , [04-RecordEntryMaster].Pass
    , [04-RecordEntryMaster].STATUS
    , [07-ScheduleMaster].ScheduleMonth
    , [07-ScheduleMaster].Type
    , [02-tblStaff].[Schedule Group]
    , [07-ScheduleMaster].ScheduleYear
    , [09-tblShift].Shift
    , Left([04-RecordEntryMaster] ! [Course Name], 20) AS [SOP No]
    , Right([04-RecordEntryMaster] ! [Course Name], Len([04-RecordEntryMaster] ! [Course Name]) - 20) AS Description
FROM [07-ScheduleMaster]
INNER JOIN (
    [10-Department] INNER JOIN (
        [04-RecordEntryMaster] INNER JOIN (
            [09-tblShift] INNER JOIN [02-tblStaff] ON [09-tblShift].[Schedule Group] = [02-tblStaff].[Schedule Group]
            ) ON [04-RecordEntryMaster].[EE Number] = [02-tblStaff].[EE Number]
        ) ON [10-Department].[Job Code Description] = [02-tblStaff].[Job Code Description]
    ) ON [07-ScheduleMaster].ScheduleCourse.Value = [04-RecordEntryMaster].[Course Name]
WHERE (
        (([04-RecordEntryMaster].[EE Name]) LIKE "*" & [Forms] ! [frmRecordEntry] ! [cboName] & "*")
        AND (([10-Department].Department) LIKE "*" & [Forms] ! [frmRecordEntry] ! [cboDepartment] & "*")
        AND (([07-ScheduleMaster].ScheduleMonth) LIKE "*" & [Forms] ! [frmRecordEntry] ! [cboMonth] & "*")
        AND (([07-ScheduleMaster].Type) = "Training")
        AND (([07-ScheduleMaster].ScheduleYear) LIKE "*" & [Forms] ! [frmRecordEntry] ! [cboYear] & "*")
        AND (([09-tblShift].Shift) LIKE "*" & [Forms] ! [frmRecordEntry] ! [cboShift] & "*")
        );

 

[ebs17]

ScheduleCourse.Value: Search with Ctrl+F in the entire text

SELECT [04-RecordEntryMaster].[EE Number] AS [EE Number], [04-RecordEntryMaster].[EE Name], [04-RecordEntryMaster].[Course Name], [02-tblStaff].[Department Cost Center], [10-Department].Department, [04-RecordEntryMaster].Frequency, [04-RecordEntryMaster].[Last Date], [04-RecordEntryMaster].[Next Date], [04-RecordEntryMaster].Pass, [04-RecordEntryMaster].Status, [07-ScheduleMaster].ScheduleMonth, [07-ScheduleMaster].Type, [02-tblStaff].[Schedule Group], [07-ScheduleMaster].ScheduleYear, [09-tblShift].Shift, Left([04-RecordEntryMaster]![Course Name],20) AS [SOP No], Right([04-RecordEntryMaster]![Course Name],Len([04-RecordEntryMaster]![Course Name])-20) AS Description
FROM [07-ScheduleMaster] INNER JOIN ([10-Department] INNER JOIN ([04-RecordEntryMaster] INNER JOIN ([09-tblShift] INNER JOIN [02-tblStaff] ON [09-tblShift].[Schedule Group] = [02-tblStaff].[Schedule Group]) ON [04-RecordEntryMaster].[EE Number] = [02-tblStaff].[EE Number]) ON [10-Department].[Job Code Description] = [02-tblStaff].[Job Code Description]) ON [07-ScheduleMaster].ScheduleCourse.Value = [04-RecordEntryMaster].[Course Name]
WHERE ((([04-RecordEntryMaster].[EE Name]) Like "*" & [Forms]![frmRecordEntry]![cboName] & "*") AND (([10-Department].Department) Like "*" & [Forms]![frmRecordEntry]![cboDepartment] & "*") AND (([07-ScheduleMaster].ScheduleMonth) Like "*" & [Forms]![frmRecordEntry]![cboMonth] & "*") AND (([07-ScheduleMaster].Type)="Training") AND (([07-ScheduleMaster].ScheduleYear) Like "*" & [Forms]![frmRecordEntry]![cboYear] & "*") AND (([09-tblShift].Shift) Like "*" & [Forms]![frmRecordEntry]![cboShift] & "*"));

For table fields, using a Value property is more of an error.

 

[NSAMSA]

I tried creating a query off of the original table that takes the multi-select values and makes them a list and using that as my source. While that query continues to work, the query in questions continues to fail. How can I work around this, as the nature of this database is to enter multiple values attached to a month and year, and to do each one separate would become quite cumbersome.

Also, why would the query where I make a list work even though it uses the value property?

 

[GPGeorge]

I tried creating a query off of the original table that takes the multi-select values and makes them a list and using that as my source. While that query continues to work, the query in questions continues to fail. How can I work around this, as the nature of this database is to enter multiple values attached to a month and year, and to do each one separate would become quite cumbersome.

Also, why would the query where I make a list work even though it uses the value property?

Multi-value fields are the least flexible approach to handling both one-to-many and many-to-many relationships. The more appropriate way is to normalize the table into two or three tables, depending on which relationship is involved. If the "multiple values attached..." are also a standard list of items, they also belong in a separate table. Then you'd have what is called a junction table in which the other two tables are used to create the records you now have in that ugly MVF.

Look up and study normalization. Normalize this database. Your tasks will be easier to accomplish.

 

[mike60smart]

I tried creating a query off of the original table that takes the multi-select values and makes them a list and using that as my source. While that query continues to work, the query in questions continues to fail. How can I work around this, as the nature of this database is to enter multiple values attached to a month and year, and to do each one separate would become quite cumbersome.

Also, why would the query where I make a list work even though it uses the value property?

Can you upload a copy of your database with no confidential data?

 

[The_Doc_Man]

Just for the record, when you get an input box with "Enter Parameter Value" followed by the name of something, this message invariably means one of a few things. Either (a) you spelled the "name of something" incorrectly or (b) you incorrectly identified how to find it or (c) it is something that never existed in the first place. In fact, all of those are facets of the same problem: Can't find something you specified. So when you get the "dreaded parameter box" check your spelling and verify that the named parameter is valid.

In your case, the thing it can't find is "07-ScheduleMaster.ScheduleCourse.Value"

 

[Gasman]

TBH, I never knew fields have a ,Value property?

Why is this the ONLY field you have used .Value with?

 

[Josef P.]

Perhaps a Multi Value Field is used.

 

[Gasman]

Perhaps a Multi Value Field is used.

So how do you know which value is being referred to, or is it all of them like IN()
I think I can have them in 2007, but never used them.

 

[Josef P.]

[OT: MVF]

SQL:
SELECT tTest.id, tTest.T, tTest.Properties.Value, tProperties.PropName
FROM tTest INNER JOIN tProperties ON tTest.Properties.Value = tProperties.idProp;


vs "real" n:m table:

Note: "Value" is only for better comparison, please never use such a field name.

 

[Gasman]

Thank you @Josef P.
I have just discovered this while Googling for MVF and 2007, so will inspect that when I have time.

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk