Query Bug

[Sameal]

I have a table with a field named 'RFQ' which is a double field with numbers that are 10-digits long. The problem I'm running into is in one instance I have found that one of my queries is not working altogether correctly.

There are two different records in my tblMaterialDetails where the RFQ field is equal to 1354001001, but when I run a query that uses that field as the criteria it simply will not pull back that record and instead skips it altogether.

While trying to figure this out, I took out the criteria in the query and ran the query and it still didn't pull all the records out of the table? What gives?

The only last thing I am wondering is if a calculated field that is set to use a specific field. If a record didn't have any value for that particular field would it make the query leave out that record???

SELECT tblMaterialDetails.MaterialDetailID, [tblMaterials.Material] & " - " & [tblAlloys.Designation] AS Material, tblMaterialDetails.Specification, tblSuppliers.Supplier, [LeadTime] & " day" & IIf([LeadTime],"s","&quot AS Lead, IIf(IsEmpty([Length]) Or IsNull([Length]),Format(([Weight]*[PriceLb])+[Shipping],"Currency&quot,Format(([Length]*[PriceFt])+[Shipping],"Currency&quot) AS Cost, tblMaterialDetails.RFQ
FROM tblSuppliers INNER JOIN ((tblMaterialDetails INNER JOIN tblAlloys ON tblMaterialDetails.Material = tblAlloys.AlloyID) INNER JOIN tblMaterials ON tblAlloys.Material = tblMaterials.MaterialID) ON tblSuppliers.SupplierID = tblMaterialDetails.Supplier
WHERE (((tblMaterialDetails.RFQ)=[Forms]![frmSWATQuote]![txtDay] & [Forms]![frmSWATQuote]![txtPack] & [Forms]![frmSWATQuote]![txtPart]));

 

[THoey]

I am curious about the following statement:

The only last thing I am wondering is if a calculated field that is set to use a specific field. If a record didn't have any value for that particular field would it make the query leave out that record???

Does this mean that upon record creation, one of the fields on the form that are used to create the RFQ field in the table were left blank? I am not sure how Access would handle that, whether the RFQ would be all blank due to the NULL value or whether it would just create a "shorter" RFQ value. But if you can look in the table and see a valid RFQ value, then it should have handled it.

If you are talking about in the search, yes, it won't match what is in the table. If you are trying to get this to find all records that match the Day and the Part values , but don't care about the pack value, then you are going to have to look into the "&" wildcard character and probably the IIF to test the field to see if it is blank.

Hope that helps... "Alcohol is our friend, and it's about time we had more friends over."

Terry M. Hoey

 

[Sameal]

Yes your thinking on the same track. Sorry my wording was kind of hard to understand. I will play with some of those things you talked about today and post back about it. I figured out how to fix my problem the other day but I still haven't figured out what happened.

tblMaterialDetails holds records of individual material orders that must be attached to a quote. In this table there is a field named RFQ that has the full 10-digit RFQ number used to refrence quotes through the system.

The form that uses this table uses a listbox control to display the results of a query from tblMaterialDetails. The query above is that query and it basically pulls out ever record from tblMaterialDetails that matches the RFQ# that is currently displayed on the frmSWATQuote.

Somehow two identical entries got into the tblMaterialDetails when I posted this problem but I noticed that the list box was only displaying one of those records. So I went into the query and everything was in order as before and I switched from Design view to Normal View and that first record was not there even though I KNEW it existed in the table and had the exact RFQ#. Since the RFQ field is the only field I had a criteria set on I was extremelly confused to why the query was missing it. I then deleted the RFQ criteria and switched to normal view and the second material entry with the same RFQ# still didn't show up. I fixed things by deleting out the one that was the copied one and everything began working. But this leaves two problems that may be serious.

What placed a second copy into the table? Why doesn't my query work in situations where the second copy is inserted into the table?

Hope this lets anyone reading this thread understand more what I'm having problems with.

 

[Sameal]

I went into the tblMaterialDetails and found that some of the tables were not placing a 0 as the default value so I updated all this. However I thought this was causing my calculated fields in the query to fail thus not returnning a record. However...After changing them the query was still not working. It's as if the query isn't even seeing the entire table?