Need help with SQL

[BJS]

I'm having trouble retrieving records with one of my unbound text boxes.

cboCountryId is one of the unbound combo boxes on the main form.

The main form has a subform on it, which displays data based on criteria selected in the unbound combo boxes on the main form.

All the unbound combo boxes on the main form work great for the SQL. Only the cboCountryId does not work.

The country table contains the following fields:
CountryId (Primary Key - Text): CA
Country (Text): Canada

tblCountry is bound to tblShipments:
CountryId (Foreign Key - Text): CA


Here are the two bits of code that are causing me grief:

If Not IsNull(Me.cboCountryId) Then
If Not strSQLFilter = "" Then
strSQLFilter = strSQLFilter & " AND tblShipments.CountryID = " & [cboCountryId]
Else
strSQLFilter = strSQLFilter & " tblShipments.CountryID = " & [cboCountryId]
End If
End If


If strSQLFilter = "" Then
strSQL = "SELECT DISTINCTROW tblShipments.ShipmentID, tblProduct.Description AS Product, tblVessels.Description AS Vessel, tblShipments.SAPReleaseNum, tblCustomer.Description AS Customer, tblSource.Description AS Source, tblPort.Description AS Port, tblShipments.[CountryID] AS Country, tblShipments.Volume, tblShipments.SellingPrice AS Price FROM (((((((tblShipments LEFT JOIN tblCustomer ON tblShipments.CustomerID = tblCustomer.CustomerId) LEFT JOIN tblPort ON tblShipments.PortID = tblPort.PortID) LEFT JOIN tblProduct ON tblShipments.ProductID = tblProduct.ProductID) LEFT JOIN tblSeller ON tblShipments.SellerID = tblSeller.SellerID) LEFT JOIN tblSource ON tblShipments.SourceID = tblSource.SourceID) LEFT JOIN tblVessels ON tblShipments.VesselID = tblVessels.VesselID) LEFT JOIN tblTransactionType ON tblShipments.TransTypeID = tblTransactionType.TransTypeID)" & _
"ORDER BY tblShipments.VesselID;"
Else
strSQLFilter = strSQLFilter
strSQL = "SELECT DISTINCTROW tblShipments.ShipmentID, tblProduct.Description AS Product, tblVessels.Description AS Vessel, tblShipments.SAPReleaseNum, tblCustomer.Description AS Customer, tblSource.Description AS Source, tblPort.Description AS Port, tblShipments.[CountryID] AS Country, tblShipments.Volume, tblShipments.SellingPrice AS Price FROM (((((((tblShipments LEFT JOIN tblCustomer ON tblShipments.CustomerID = tblCustomer.CustomerId) LEFT JOIN tblPort ON tblShipments.PortID = tblPort.PortID) LEFT JOIN tblProduct ON tblShipments.ProductID = tblProduct.ProductID) LEFT JOIN tblSeller ON tblShipments.SellerID = tblSeller.SellerID) LEFT JOIN tblSource ON tblShipments.SourceID = tblSource.SourceID) LEFT JOIN tblVessels ON tblShipments.VesselID = tblVessels.VesselID) LEFT JOIN tblTransactionType ON tblShipments.TransTypeID = tblTransactionType.TransTypeID)" & _
" WHERE " & strSQLFilter & "ORDER BY tblShipments.VesselID;"
End If


Forms!frmMain!subfrmMain.Form.RecordSource = strSQL
Forms!frmMain!subfrmMain.Form.Requery

NOTE: As soon as I pick an item from cboCountryId as criteria, no records are retrieved, but I know that there should be matching records.

I am obviously missing something? Any ideas?
The only difference between the cboCountryId combo box and all the other ones, is that the primary key is a text field.

Looking forward to some help with this one.......THANKS!

 

[BJS]

Well, I'm still trying to figure this one out. I think it is a problem with this statement:

If Not IsNull(cboCountryId) Then
If Not strSQLFilter = "" Then
strSQLFilter = strSQLFilter & " AND tblShipments.CountryID = " & [cboCountryId]
Else
strSQLFilter = strSQLFilter & " tblShipments.CountryID = " & [cboCountryId]
End If

I created a text box on my for to display the value of "strSQLFilter"

If I select criteria for Source, Port, Country and Date, then
strSQLFilter = tblShipments.SourceID = 1 AND tblShipments.PortID = 17 AND tblShipments.[CountryID] = KR AND tblShipments.BLDate Between #1/1/2003 12:00:01 AM# AND #1/2/2003#

I get prompted to enter a parameter value KR

The CountryID is a text field. I wonder if I need to put some quotes around it somehow to read it as text? Any ideas?

THANKS!

 

[BJS]

PLEASE HELP! I am doing some testing to narrow down the problem.

I created a simple query: using ShipmentId field and CountryId field, both from tblShipments.

I set the criteria for the CountryId = [Forms]![frmMain]![cboCountry]. cboCountry is an unbound combo box on frmMain.

When running the query, frmMain is open and I have selected an Item from cboCountry. The query retrieves no records, but I know there are matching records for the country I chose in the combo box in tblShipments.

tblCountry contains fields:
CountryID (primary key, text)
Description

tblShipments contains fields:
ShipmentID (primary key, autonum)
CountryID (foreign key, text)

PLEASE PLEASE HELP! THANK YOU!!

 

[BJS]

THANK GOODNESS....HELP HAS ARRIVED!

Pat, I have the property settings set for the combo box, exactly as you describe.

I put a text box on my form to display the value of strSQLString, which is my criteria in the SQL Statment behind the FILTER BUTTON on my form:

Here is what the text box displays as my criteria when I choose a value from the combo boxes "cboPort", "cboCountry" and the Start and End Dates. I'm pretty sure that the error is "tblShipments.[CountryID] = 'cboCountry' AND.....

It is not reading the value of the combo box as text, it seems.


tblShipments.PortID = 17 AND tblShipments.[CountryID] = KR AND tblShipments.BLDate Between #1/1/2003 12:00:01 AM# AND #1/2/2003#

I get prompted to enter a parameter value for KR.

 

[BJS]

Pat, I replaced my code with yours and this is now the result of my strSQLString:

tblShipments.PortID = 17 AND tblShipments.CountryID = "" AND tblShipments.BLDate Between #1/1/2001 12:00:01 AM# AND #1/2/2003#

It is still not seeing the value of the combo box item I entered into the cbocountry field.

Any more thoughts on this. I REALLY APPRECIATE YOUR HELP.

 

[BJS]

Pat.....your code did work for me after all. The first time I put it in, it still didn't read the value of the combo box. I tried it again later, and it worked perfect!

THANK YOU VERY VERY VERY VERY MUCH!
You have taken the stress out of my life with this one!
I can't believe how much time I spent trying to figure this one out!