Erroneous record shows up for no reason

[connie]

I have a form with combo boxes that is based on a query, and the form outputs to a customized report based on what options the user selected from the combo boxes. For the 1st query I entered the following criteria so that the user can leave certain combo boxes blank if desired and the query won't return no results:

[Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

Which is all fine and works great. The report is actually based on the 2nd query, which pulls the info from the 1st query and adds a couple of fields to it.

Here is my odd issue. When a user selects "CPD" as the Department from the Dept Combo box, and then any Serial # from the Serial # combo, the report returns all the desired records but adds 1 record from the CPD dept. that has no Serial #. So for example if user asks to view all items with the Serial # ABC123 in Department CPD, it will show all of those plus this mop bucket with no Serial #. Every time, no matter what Serial # is chosen.

Upon analyzing the database there are multiple other records in Department CPD with no Serial Number, but it doesn't pull any of those. Any idea why it would be adding this one record to every report in that Department regardless of what Serial # is specified?

 

[georgedwilkinson]

This "sounds" like an issue with a program loop, either a variable not being initialized before the loop or a loop that allows "one more" iteration after the last desirable record has been processed. Do you have a loop in VBA that generates the result set?

 

[connie]

All I have in VBA for this form is:

Option Compare Database
Private Sub cmdCancel_Click()
    DoCmd.Close acForm, "frmBuildCustomReport"
End Sub
Private Sub cmdOK_Click()
    If Me!Check25.Value = True Then
    DoCmd.OpenReport "rptCustomBuiltSummed", acViewPreview
    DoCmd.Close acForm, "frmBuildCustomReport"
    Else
    DoCmd.OpenReport "rptCustomBuilt", acViewPreview
    DoCmd.Close acForm, "frmBuildCustomReport"
    End If
  
End Sub

 

[georgedwilkinson]

What about the VBA for the 2 reports?

Question is, where is the record-set coming from on the report with the problem (whether it is VBA or SQL or whatever)?

 

[connie]

Nope, no VBA for the reports. Per my first post they are coming from a query based on another query. The problem is, I can no longer open the 1st query any more because once Access converted my:

[Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

to a bunch of cascading criteria, the query works but I can't open it in Design View anymore, I get "Query too complex."

 

[MSAccessRookie]

Nope, no VBA for the reports. Per my first post they are coming from a query based on another query. The problem is, I can no longer open the 1st query any more because once Access converted my:

[Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

to a bunch of cascading criteria, the query works but I can't open it in Design View anymore, I get "Query too complex."

Are you able to view it in SQL Mode?

 

[connie]

Ok, yes I am! Here is the SQL of the first query, qryBuildCustomReport

SELECT tblEquipmentDatabase.CAD_ID, tblEquipmentDatabase.Dept_Name,
tblEquipmentDatabase.Description, tblEquipmentDatabase.Type_Funding, tblEquipmentDatabase.FI,
tblEquipmentDatabase.Manufacturer, tblEquipmentDatabase.Model, tblEquipmentDatabase.Room_No,
tblEquipmentDatabase.RecordID
FROM tblEquipmentDatabase
WHERE (((tblEquipmentDatabase.CAD_ID)=[Forms]![frmBuildCustomReport]![cboCADID] Or
(tblEquipmentDatabase.CAD_ID) Like [Forms]![frmBuildCustomReport]![cboCADID] Is Null) AND
((tblEquipmentDatabase.Dept_Name)=[Forms]![frmBuildCustomReport]![cboDeptName] Or
(tblEquipmentDatabase.Dept_Name) Like [Forms]![frmBuildCustomReport]![cboDeptName] Is Null) AND
((tblEquipmentDatabase.Description)=[Forms]![frmBuildCustomReport]![cboDescription] Or
(tblEquipmentDatabase.Description) Like [Forms]![frmBuildCustomReport]![cboDescription] Is Null) AND
((tblEquipmentDatabase.Type_Funding)=[Forms]![frmBuildCustomReport]![cboTypeFunding] Or
(tblEquipmentDatabase.Type_Funding) Like [Forms]![frmBuildCustomReport]![cboTypeFunding] Is Null) AND
((tblEquipmentDatabase.FI)=[Forms]![frmBuildCustomReport]![cboFurnishInstall] Or (tblEquipmentDatabase.FI)
Like [Forms]![frmBuildCustomReport]![cboFurnishInstall] Is Null) AND ((tblEquipmentDatabase.Manufacturer)=
[Forms]![frmBuildCustomReport]![cboManufacturer] Or (tblEquipmentDatabase.Manufacturer) Like [Forms]!
[frmBuildCustomReport]![cboManufacturer] Is Null) AND ((tblEquipmentDatabase.Model)=[Forms]!
[frmBuildCustomReport]![cboModel] Or (tblEquipmentDatabase.Model) Like [Forms]![frmBuildCustomReport]!
[cboModel] Is Null) AND ((tblEquipmentDatabase.Room_No)=[Forms]![frmBuildCustomReport]![cboRoomNumber] Or
(tblEquipmentDatabase.Room_No) Like [Forms]![frmBuildCustomReport]![cboRoomNumber] Is Null) AND ([Forms]!
[frmBuildCustomReport]![cboCADID]=[CAD_ID] Or [Forms]![frmBuildCustomReport]![cboDeptName]=[Dept_Name]
Or [Forms]![frmBuildCustomReport]![cboRoomNumber]=[Room_No] Or [Forms]![frmBuildCustomReport]!
[cboManufacturer]=[Manufacturer] Or [Forms]![frmBuildCustomReport]![cboModel]=[Model] Or [Forms]!
[frmBuildCustomReport]![cboDescription]=[Description] Or [Forms]![frmBuildCustomReport]![Label16]=
[Type_Funding] Or [Forms]![frmBuildCustomReport]![cboFurnishInstall]=[FI]));

And this is the SQL for the 2nd query--qryCustomDataExtended--the one that reads from qryBuildCustomReport and then feeds the form:

SELECT qryBuildCustomReport.CAD_ID, qryBuildCustomReport.Dept_Name, qryBuildCustomReport.Description,
qryBuildCustomReport.Type_Funding, qryBuildCustomReport.FI, qryBuildCustomReport.Manufacturer,
qryBuildCustomReport.Model, qryBuildCustomReport.Room_No, tblEquipmentDatabase.Item_Qty,
tblEquipmentDatabase.Unit_Cost, tblEquipmentDatabase.Ext_Cost, tblEquipmentDatabase.Date_Modified,
tblEquipmentDatabase.Room_Name
FROM tblEquipmentDatabase INNER JOIN qryBuildCustomReport ON tblEquipmentDatabase.RecordID =
qryBuildCustomReport.RecordID;

 

[georgedwilkinson]

Upon further analysis of your original post, I'm guessing that your where clause is allowing this extra field because that field (Serial #?) is null, though I see absolutely no reference to such a field in your queries.

 

[connie]

Oh my gosh, I'm SO sorry! I was staring at that code so long my eyes started crossing

The "Serial #" I was referring to is actually CAD_ID...

 

[namliam]

[Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

You cannot do this, this is wrong! " Or Like... is null" ??? This is DOOMED to fail !
If anything it should read

[Forms]![frmBuildCustomReport]![cboDepartmentName] Or [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

I.e. No like, but even with this is just plain WRONG

[rant]
AND PLEASE if you post SQL on the forum, format it !!! to be somewhat readable dont just splash it on here
[/rant]
Just look at what the code does....

(  CAD_ID =    [Forms]![frmBuildCustomReport]![cboCADID] 
Or CAD_ID Like [Forms]![frmBuildCustomReport]![cboCADID] Is Null )

Note the reformatting I did and the removing of the otherwize useless () and table name...

Can you see now with this reformatting what this code does AND why it is SO WRONG??

With all these fields I am 100% sure you would be better of just making this query in code... Just having the (splashed) normal select as default

SELECT tblEquipmentDatabase.CAD_ID, tblEquipmentDatabase.Dept_Name,
tblEquipmentDatabase.Description, tblEquipmentDatabase.Type_Funding, tblEquipmentDatabase.FI,
tblEquipmentDatabase.Manufacturer, tblEquipmentDatabase.Model, tblEquipmentDatabase.Room_No,
tblEquipmentDatabase.RecordID
FROM tblEquipmentDatabase

(above REALLY needs some reformatting ! but I am to lazy to do your job for you)

and adding the where on the fly in code using something like:

Mysql = Defaultsql
If Not isnull (ThisCombobox) then
   mysql = mysql & " and this field = " & ThisCombobox
end if
If not isnull (thatCombobox) then
   mysql ....
endif
etc.
etc.
Currentdb.Querydefs("YourQuery").sql = mysql 

then open form or what ever you want it to do.

Hope that makes sense?

Note: that if you go this way... Dates and Strings need a little special attention.
Numbers look like this:
mysql = mysql & " and this field = " & ThisCombobox
Strings/texts:
mysql = mysql & " and this field = '" & ThisCombobox & "'"
or
mysql = mysql & " and this field = """ & ThisCombobox & """"

Dates:
mysql = mysql & " and this field = #" & ThisCombobox & "#"

Good luck!

If you want a more worked out sample, search the references forum for "search form" and you will find some.

 

[connie]

I was trying to figure out why my code was going in a box with scrollbars...I was hitting php instead of the Code button. I didn't know, I'm sorry! I actually was sitting there and deleting the extra space at the end of each line and hitting Enter after each line to try and un-messy it, so I didn't just "splash" it on there. Don't yell at the noob

Okay, the reason why I have Or Like...Is Null is because I want to tell Access that if any of the Combo boxes are left blank, to return all of the values for that field that jive with the other cbo box selections. If you scroll all the way to the bottom of this page - which is an Access tutorial - section 4.3 Allowing Null Entries, that is what I followed. So that is wrong?

This was working except for a couple of random records that probably have empty strings...but I'm willing to fix it all if that's wrong, of course. I don't understand that MySQL code example all...*hair pulling*

Thx all 4 your help

 

[namliam]

I I actually was sitting there and deleting the extra space at the end of each line and hitting Enter after each line to try and un-messy it, so I didn't just "splash" it on there. Don't yell at the noob

1) Just entering semi-randomly doesnt make for formatting code...
2) I was no where near yelling IMHO, just emphasizing... I applogize if I upset you or something, did not mean to do so.

Okay, the reason why I have Or Like...Is Null is because I want to tell Access that if any of the Combo boxes are left blank, to return all of the values for that field that jive with the other cbo box selections. If you scroll all the way to the bottom of this page - which is an Access tutorial - section 4.3 Allowing Null Entries, that is what I followed. So that is wrong?

Well, I must admit I
1) Dont know that tutorial
2) Hate the average tutorial as they give "tricks" not knowledge... Much this one too

Have you read beyond the solution given??

When you do this and run the query for the first time you will find that Access has changed the way your criteria were written from the text above to something more complex. The criteria grid now contains several rows of entries. Leave it alone! Access has just broken down your ... Or Like ... Is Null statement into its component parts, listing all possible combinations of null and not-null entries on separate lines. The query needs this but, thankfully, you can type it out the quick way!

This new notation is STILL not quite proper SQL as it depends on a certain way that SQL/Jet handles Nulls, instead of the like you can use = or<> or whatever, but this is ACCESS/JET specific and will not work in any other SQL ( I havent tested, but basicaly it is just BAD SQL ! and thus shouldnt be used anyways even if supported)

The way this errors out is because "it is to complex" though it looks "simple" right?? WRONG, its BAD SQL that looks simple but is very complex...

Consider this simplyfied example:
( Field1=cbo1 or cbo1 like Null = Null )

Pretty simple yes? A simple or... 2 values to check...
( Field1=cbo1 or cbo1 like Null = Null )
AND ( Field2=cbo2 or cbo2 like Null = Null )

Still looks simple but what ACTAULLY happens is this:
(Field1=cbo1 AND Field2=cbo2 )
Or ( cbo1 like Null = Null AND Field2=cbo2 )
Or ( Field1=cbo1 AND cbo2 like Null = Null )

This already looks a little more complex, yes?? And this is with 2 values only
Generaly speaking each field has two options, 1 or 2 (filled or Null)
11
21
12
22
Make the options, total 4 (2 x 2), now add a 3rd
111
112
121
122
211
212
221
222
Thats the options for 3 fields (8 total, 2x2x2), now 4
1111
1112
1121
1122
1211
1212
1221
1222
2111
2112
2121
2122
2211
2212
2221
2222
For 4 fields 16 options (2x2x2x2)! See how the options explode and things get complex fast 5, 32 6 64, 7 128 etc...

And that is not even counting your last OR bit is confuses the hell out of me

This was working except for a couple of random records that probably have empty strings...but I'm willing to fix it all if that's wrong, of course. I don't understand that MySQL code example all...*hair pulling*

Thx all 4 your help

Empty strings and Nulls can be confusing, while this code works... if you feel (kindoff) comfertable flying with this in the face of convention and stuff alike... Then fly with it...

What is the problem with the empty strings? They are showing up when they shouldnt or what???

Using the "proper" way will reduce complexity and thus the fail or 'unknow' factors considerably...
The idea for the MySQL is this...

mysql = "" ' Always start with a blank SQL
mysql = mySQL & " SELECT ... "
mysql = mySQL & " , ... "
mysql = mySQL & " , ... " ' make sure to keep this part READABLE
Etc...
mysql = mySQL & " From ... "
mysql = mySQL & " where 1=1 " ' this is a dummy where clause that always returns true... thus simply returns all records...

Now in code you check if the user entered something on your form

If Not isnull Forms("frmBuildCustomReport")!cboDepartmentName then
    ' then append the where for this combo
    mysql = mysql & " AND CAD_ID = " & Forms("frmBuildCustomReport")!cboDepartmentName
end if

Rince and repeat for each combo box.

Then at the end write the build sql in mySQL into the query using

Currentdb.Querydefs("YourQuery").sql = mysql

As a result you dont need all the "OR" parts in your SQL to check for Null values. and your query for only 1 value will be very simple...

 

[connie]

I'm revisiting this issue as I had a million other things to do in between...okay, so 1st thank you SO much for your help, esp. namliam, whom I apparently can't leave any more reputation for until I "spread some more around"...

Here's the deal. I need the query to treat combo boxes where nothing was selected as "show me everything in that category that relates to the other combo boxes' selections." However, if someone selects a certain manufacturer, it is giving all of that manufacturer plus the fields where there is no manufacturer at all...because it's treating my query exactly as it's written, obviously, which is

[I][Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null[/I]

Which I got scolded for I don't know if they're blanks or empty strings or what, but these records are showing up where they don't belong and it's probably bc my query is whack.

This part of namliam's suggestion I get:

If Not isnull Forms("frmBuildCustomReport")!cboDepartmentName then
    ' then append the where for this combo
    mysql = mysql & " AND CAD_ID = " & Forms("frmBuildCustomReport")!cboDepartmentName
end if

But the mySQL part is Greek to me??

 

[namliam]

[I][Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null[/I]

Which I got scolded for

Well scolded is a strong word as there seem to be tutorials which enduce this kind of bad SQL, same way that M$ themself promote the use of spaces or special characters all over the place, but in fact is generaly accepted to be "bad form" because of many reasons.

This part of namliam's suggestion I get:

If Not isnull Forms("frmBuildCustomReport")!cboDepartmentName then
    ' then append the where for this combo
    mysql = mysql & " AND CAD_ID = " & Forms("frmBuildCustomReport")!cboDepartmentName
end if

But the mySQL part is Greek to me??

OK the point is... You make a default query, the one you have without all the complex where stuff like:
[Forms]![frmBuildCustomReport]![cboDepartmentName] Or Like [Forms]![frmBuildCustomReport]![cboDepartmentName] Is Null

See my previous post on how that gets out of hand and "to complex" fast

Take this "simple" sql and format it like this:

mysql = "" ' Always start with a blank SQL
mysql = mySQL & " SELECT ... "
mysql = mySQL & " , ... "
mysql = mySQL & " , ... " ' make sure to keep this part READABLE
Etc...
mysql = mySQL & " From ... "
mysql = mySQL & " where 1=1 " ' this is a dummy where clause that always returns true... thus simply returns all records...

Then for each combobox you add something like so:

If Not isnull Forms("frmBuildCustomReport")!cboDepartmentName then
    ' then append the where for this combo
    mysql = mysql & " AND CAD_ID = " & Forms("frmBuildCustomReport")!cboDepartmentName
end if

Which checks if the combobox is null, if it is then no WHERE part is added.
If it isnt then the IF is 'executed' and the where appended, making for a MUCH simpler final sql.

This sql then you write into your query using this:

Currentdb.Querydefs("YourQuery").sql = mysql

YourQuery is then executed by a form/report to display the results of the "simplefied complex" query.
The point of this all is to take out each "superfulous" (sp?) OR syntax and remove all the options that the SQL has to check.

I really dont know how else to explain this?