Dealing with Forward Slashes?

[Stang70Fastback]

Hello all! Simple question. I have the following code:

        If DCount("Employee", "ManpowerDataEmployee3", "Discipline = '" & Me.DisciplineCOM & "'") <> 0 Then
            DoCmd.OpenReport "ManpowerSummaryDiscipline", acViewPreview, , "Discipline = '" & Me.DisciplineCOM & "'"
            DoCmd.Maximize
        Else
            MsgBox "There are no engineers currently assigned to this discipline.", vbInformation, "No Engineers"
        End If

It works perfectly fine when the Me.DisciplineCOM field is a string such as "Architect" or "Civil", but I run into issues when the field is a string such as "QA / QC" or "Mechanical / Plumbing."

It's clearly an issue with the forward slash. I've tried "escaping" the forward slash using Replace(Me.DisciplineCOM, "/", "///") but that still doesn't work. What am I doing wrong? How do I resolve this issue WITHOUT renaming the fields themselves?

Thanks guys!

 

[spikepl]

try

"Discipline = " & Chr(34) & Me.DisciplineCOM & Chr(34)

 

[pbaldy]

If that doesn't work try

"Replace(Discipline, '/', '') = '" & Replace(Me.DisciplineCOM, "/", "") & "'"

 

[Stang70Fastback]

Neither of those options seem to work. In either case I still wind up with a blank report with the #Type error

 

[pbaldy]

Can you post a sample db that exhibits the problem? I just did a quick and dirty test and this worked:

DoCmd.OpenReport "tblNumbers", acViewPreview, , "TextField = 'test5/test6'"

which would indicate the slash isn't the problem.

 

[Stang70Fastback]

I'll try to separate stuff out so I can post something. I have like 10 fields, and only the two with the slashes cause the problem. I've encountered this issue before, I think, but I can't remember how I solved it. If you explicitly name the string, it's fine, but if it's a reference to a control that contains the string, and that string contains a strange character, that's when I run into issues.

 

[pbaldy]

Okay, because I got this to work as well:

DoCmd.OpenReport "tblNumbers", acViewPreview, , "textfield = '" & Me.Text29 & "'"

 

[Stang70Fastback]

Well screw you then, lolol. Hmmm... let me do one last round of idiot-checking and if I still can't find it I'll snip things apart and post something

 

[pbaldy]

Just me, or the horse I rode in on too?

 

[Stang70Fastback]

Okay, so I figured it out and I feel dumb.

It WAS just the two fields with the slashes. Someone edited the tables and made exactly TWO changes: They changed the "Mechanical/Plumber" field to just "Mechanical" and the QA/QC field to "QA / QC" for some dumb reason.

I hate people. Time to add warning messages to the field-changing tool...

Thanks for the help. Without you confirming that it should work I'd have still been stumped.

 

[pbaldy]

No problem. I've never given users a field-changing tool myself.

 

[spikepl]

And this is what happens if we guess at undefined "issues" instead of asking what exactly the problem is.

 

[Galaxiom]

Someone edited the tables and made exactly TWO changes: They changed the "Mechanical/Plumber" field to just "Mechanical" and the QA/QC field to "QA / QC" for some dumb reason.

The problem is deeper than you think. In effect the database is storing information in the name of a field. Every time you need to add another EmployeeDiscipline you need to add a field to the Employee table.

A proper relational structure stores the Disciplines in a separate table as a DisciplineID and a DisciplineName. A junction table handles the many-to-many relationship between EmployeeID and DisciplineID.

With this structure, new disciplines are added to the system by adding records to the Discipline table.

BTW. Using special characters in any object name is best avoided.