Strsql - Multiple matching conditions

[CaiSupport]

I have the following working code to create a file with a price matching to the price file on Item number and price level. I now need to check for the price based on Customer number and item number besides Item number and Price Level. The pricing will be one or the other, but not both. This is my code.

strSQL = _
"SELECT #" & dteStart & "# AS Expr1, " & _
"CustomerService.[Customer Number], CustomerService.[Item Number], " & _
"IM1_InventoryMasterfile.ProductLine, " & _
"IM1_InventoryMasterfile.ItemDescription, " & _
"IMB_PriceCode.DiscountMarkupPriceRate1, CustomerService.[Price Level], " & _
"CustomerService.Quantity, IM1_InventoryMasterfile.TaxClass, " & _
"IM1_InventoryMasterfile.SalesUM, CustomerService.ID, " & _
"CustomerService.[Next Date], CustomerService.[Frequency] " & _
"FROM ((AR1_CustomerMaster RIGHT JOIN CustomerService " & _
"ON AR1_CustomerMaster.CustomerNumber=CustomerService.[Customer Number]) " & _
"INNER JOIN IMB_PriceCode " & _
"ON (CustomerService.[Price Level]=IMB_PriceCode.ItemCustomerPriceLevel) " & _
"AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber)) " & _
"LEFT JOIN IM1_InventoryMasterfile " & _
"ON CustomerService.[Item Number] = IM1_InventoryMasterfile.ItemNumber " & _
"WHERE ((AR1_CustomerMaster.CustomerNumber <> "" "") AND " & strWHERE & ") " & _
"ORDER BY AR1_CustomerMaster.CustomerNumber, " & _
"IM1_InventoryMasterfile.ProductLine " & _
";"

It is the IMB_PriceCode file that I want to match on either customer/item or price level/item.

Thank you,

 

[CaiSupport]

Everytime I add my other condition, I do not get any records. Please help.
Original works
"INNER JOIN IMB_PriceCode " & _
"ON (CustomerService.[Price Level]=IMB_PriceCode.ItemCustomerPriceLevel) " & _
"AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber)) " & _

Added or condition - No records created and No error
"INNER JOIN IMB_PriceCode " & _
"ON (((CustomerService.[Price Level]=IMB_PriceCode.ItemCustomerPriceLevel) " & _
"AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber)) " & _
"OR ((CustomerService.[Customer Number]=IMB_PriceCode.CustomerNumber) " & _
"AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber))) " & _
"LEFT JOIN IM1_InventoryMasterfile " & _

 

[rubbernilly]

Can you have an "Or" statment in the join? Hmm...

What you are doing is probably a lot more elegant than how I would go about trying this.

I would probably create a union query, with the first SELECT statement having a join type of cust/item, and the second SELECT statement having a join type of price level/item.

Would this accomplish what you need?

 

[CaiSupport]

I am not an access person and someone did this code for us a year ago. I really need detail instruction if you could please. The first post shows the code as it is.

Thank you for your quick response.

 

[rubbernilly]

1. Put the code back the way it was to begin with, where it was working.
2. Put a break point in the code on the line that begins...

strSQL =

3. Run the code.
4. When the code breaks at this line, make sure that you have your Debug Toolbar showing (View>Toolbars), and choose to step through the code.
5. Once the strSQL variable has been filled with the SQL
Take the working SQL to the Query builder window. Go to your SQL view and paste the SQL in.
6. Keep the SQL on your clipboard
7. Go to the design view and make the appropriate Join changes so that your tables not link on the other potential criteria.
8. Go to the SQL view again, and this time put your cursor before the last semicolon and hit enter
9. Type in "UNION" and then a space and then paste in your original SQL.

If I haven't gotten myself completely turned around, this should give you a union Query. You should have all of the records where the cust/item match is made, and also where the price/item is made.

If you cannot tell from this data which is which, you can add a new field to the SQL with a particular value in each part of the UNION query. In the first, the last field in your SELECT statement would be a new field: "[JoinType] = 'Customer'", and in the second half, make it "Price".

also, look up Union Query in the Help for more information.

HTH

 

[rubbernilly]

Um... that might need to be "Customer" for the second part, and "Price" for the first... but I trust you to know your own data well enough to tell the difference and catch that.

 

[CaiSupport]

I did realize that much. I am not sure how to do the breakpoint and if I try the Union and just copy the whole STRSQL it says too many line continuations. I can get some debug stuff to display on the bottom, but it usually says out of context. (I said I do not know what I am doing)
I also get a Macros box that pops up. Thank you for your help.

 

[rubbernilly]

It sounds like you are trying to paste the code version of the SQL into the SQL view of the query. That's not exactly what I want you to do.

Here is a crash course in working with the VBE window:

Get to the VBE window where this code resides. To the left of the code window, there is a gray bar running up the side, between the border and the white area for the text of the code. If you click in this area, you will see a red dot appear, and the line associated with it will become highlighted in red (well, maroon).

If you still don't see what I am talking about, then in your VBE window, make sure you are viewing the Debug toolbar (under View>Toolbars). Put your cursor on the line that starts:

strSQL =

...and then click on the button on the Debug toolbar that looks like a white hand. This will put the red dot in, and also highlight the line. (Now that you see the red-dot, you can understand the bar I was talking about).

This is called a breakpoint. When the code encounters one of these, it will go into "break-mode," allowing you to test a number of factors. You can hover over a variable or (simple) built-in function and you will get a tool-tip type popup of what the variable is or what the function evaluates to.

NOTE: You can put a breakpoint only on an executable line of code. You can't put it on a variable declaration or function/sub declaration or line labels, for example.

The button next to the white hand should look like an arrow pointing to the center of a block of text. This is the "Step Into" button. Once your code is in break-mode, you can use this button to step line by line through the code (you can also use F8 to do this).

Make sure that you also see your Immediate Pane by pressing CTRL+G, or clicking on the button on the Debug toolbar that looks like a yellow exclamation mark over a code window (or by choosing View>Immediate Window). In this window, you can test a number of commands, or execute a line of code at a time.

You know how you got the "Out of Scope" error? That was probably because the code was not running when you asked for the value of a variable. Any variable declared within a sub or function has a life span only as long as that function or sub is running. Therefore, you have to test it while in break-mode.

So, with the break-point fixed at the strSQL line, run the code. It should stop and highlight that line in yellow.

Type this into the immediate window:

?strSQL

You should get a blank line in return.

Now hit the "Step Into" button one time so that the line of code will process. Go back to the immediate window and put your cursor on the line you typed originally (?strSQL) and hit enter again.

You should now get the SQL text itself.

MAKE SURE THAT YOU GOT THE CODE THAT WORKS.

Copy it from the IMmediate Pane and take it to the Query builder (don't copy the open and closing quotation marks that are a function of getting the data from a variable).

To clear a break-point, you can put your cursor in the line and hit the hand again, or you can simply click on the red-dot. Break-points like this do not last beyond closing and re-opening the database, either.

Post back if you need more help.

 

[CaiSupport]

Thank you for your patience. Here is more information. This code is under General Functions. It runs a report from the menu and creates this file. It asks for a date range and type of item to select.

I can get the red-dots, but if I click by the Strsql the whole statement is highlighted in red and has red dots on the side.

I also have the intermediate box on the bottom where I entered ?strSQL.

If I click on the step through button, nothing happens.
I have then clicked on the ?strSQL and nothing appears.

 

[rubbernilly]

Couple of things going on here...

You will get a red-dot beside the line with the breakpoint. However, since your strSQL line ends with the line continuation operator ("& _"), then next line is also a part of the one code line. And the next, and the next, etc. So when you put a breakpoint on that line, you will get multiple red-dots - one for every text line - because you are breaking on that one code line. Make sense?

How would you run this code originally? Would you push a button? If so, then click the button after your break-point is in place. Do you run a macro to get this code to run? If so, run the macro. Whatever you would do where you would naturally have used this code, do that. Get the code started and let it break on your break-point.

It is only in break-mode that you will be able to run the ?strSQL in the immediate window and get a valid return. It is also only in break-mode that you will be able to hover over a variable (like strSQL) and get a tool-tip type popup announcing what the variable is holding.

Again, once in break-mode, you will be able to put your cursor in the ?strSQL line in your immediate window and then hit enter. You will get a new line in the Immediate window with the contents of strSQL.

All of this is contingent on being able to get the code into break-mode. This should not be difficult: get your break-point in place, and then run the process that this code is a part of (button, macro, whatever).

Once it comes back up to the code window with the line highlighted in yellow, you are in business.

 

[CaiSupport]

Thank for your patience. You give great instructions. I now have the following code in Query1 for the time being. The pricing is correct, but I do have too many records which is probably because the data from the select did not come over. Now how do I get this into my Modules... General Functions. This is the SQL from the Query.
SELECT #10/5/2006# AS Expr1, CustomerService.[Customer Number], CustomerService.[Item Number], IM1_InventoryMasterfile.ProductLine, IM1_InventoryMasterfile.ItemDescription, IMB_PriceCode.DiscountMarkupPriceRate1, CustomerService.[Price Level], CustomerService.Quantity, IM1_InventoryMasterfile.TaxClass, IM1_InventoryMasterfile.SalesUM, CustomerService.ID, CustomerService.[Next Date], CustomerService.Frequency
FROM ((AR1_CustomerMaster RIGHT JOIN CustomerService ON AR1_CustomerMaster.CustomerNumber = CustomerService.[Customer Number]) INNER JOIN IMB_PriceCode ON (CustomerService.[Customer Number] = IMB_PriceCode.CustomerNumber) AND (CustomerService.[Item Number] = IMB_PriceCode.ItemNumber)) LEFT JOIN IM1_InventoryMasterfile ON CustomerService.[Item Number] = IM1_InventoryMasterfile.ItemNumber
WHERE (((IM1_InventoryMasterfile.ProductLine)<>"HOOD") AND ((CustomerService.[Next Date]) Between #10/5/2006# And #10/6/2005#) AND ((AR1_CustomerMaster.CustomerNumber)<>" "))
ORDER BY CustomerService.[Customer Number], IM1_InventoryMasterfile.ProductLine
UNION SELECT #10/5/2006# AS Expr1, CustomerService.[Customer Number], CustomerService.[Item Number], IM1_InventoryMasterfile.ProductLine, IM1_InventoryMasterfile.ItemDescription, IMB_PriceCode.DiscountMarkupPriceRate1, CustomerService.[Price Level], CustomerService.Quantity, IM1_InventoryMasterfile.TaxClass, IM1_InventoryMasterfile.SalesUM, CustomerService.ID, CustomerService.[Next Date], CustomerService.[Frequency] FROM ((AR1_CustomerMaster RIGHT JOIN CustomerService ON AR1_CustomerMaster.CustomerNumber=CustomerService.[Customer Number]) INNER JOIN IMB_PriceCode ON (CustomerService.[Price Level]=IMB_PriceCode.ItemCustomerPriceLevel) AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber)) LEFT JOIN IM1_InventoryMasterfile ON CustomerService.[Item Number] = IM1_InventoryMasterfile.ItemNumber WHERE ((AR1_CustomerMaster.CustomerNumber <> " ") AND ((CustomerService.[Next Date] Between #10/5/2006# AND #10/6/2005#) AND (IM1_InventoryMasterfile.ProductLine <> "HOOD")) )
ORDER BY CustomerService.[Customer Number], IM1_InventoryMasterfile.ProductLine;

 

[rubbernilly]

Hmm...

My understanding was that your original SQL returned one record... or at least one record for every Customer (or some other logical unit).

If that were true, then your Union Query should have returned two of that kind: one for the customer link and one for the price level link.

So let's back up a minute. With your original SQL (basically the first half of the Union Query), how many records do you return for each logical unit? And what is that logical unit (ie, a Customer)?

Also, how is it used in your code? What do you do with the strSQL after you have constructed it? Do you proceed to arrive at a particular price in the records returned by the SQL?

My suggestion was contingent on getting one return for the price you were looking for, so let's get that bridge crossed first.

Can you post more of the code from this procedure?

 

[CaiSupport]

The code asks for a start and end date and Product line. It then selects records from the customer service table and uses the SQL to create a file for input into another system. Here is the code.
Public Function CustomerServiceReport(blnCreateCustomerOrderFile As Boolean)
Dim intFileNum As Integer
Dim rst As DAO.Recordset
Dim strSQL As String 'SQL statement acting as recordsource for output file
Dim intSecondLast As Integer 'index of 2nd-last field in rst
Dim intLoop As Integer 'loop control variable
Dim strDate As String 'gets user's input for validation before assigning to date variable
Dim dteStart As Date, dteEnd As Date 'start & end date for report & output file
Dim strHoodOperator As String '[Product Line] EQUALS "hoods", or DOES NOT EQUAL "hoods"
Dim strWHERE As String 'WHERE clause (excluding WHERE keyword) for report & strSQL

Const strReportName = "Customer Service Report"
Const strOrderFile = "C:\ORDERFILE.CSV" 'name & path of output file

'Get and validate Start Date
Do
strDate = InputBox("1) Enter the first date you want exported.", "Enter START Date")
Loop While (Not IsDate(strDate))
dteStart = strDate

'Get and validate End Date
Do
strDate = InputBox("2) Enter the last date you want exported.", "Enter END Date")
Loop While (Not IsDate(strDate))
dteEnd = strDate

'Get operator to use in strWHERE
If (MsgBox("3) Click Yes to export only HOODS, or click No to export everything else.", _
vbYesNo, "Choose Product Line For Report And Order File") = vbYes) Then
strHoodOperator = "=" 'IM1_InventoryMasterfile.ProductLine = "HOOD"
Else
strHoodOperator = "<>" 'IM1_InventoryMasterfile.ProductLine <> "HOOD"
End If
'Build WHERE clause with dates and user's product line choice (HOOD or NOT HOOD)
strWHERE = "((CustomerService.[Next Date] Between " & _
"#" & dteStart & "# AND #" & dteEnd & "#) " & _
"AND (IM1_InventoryMasterfile.ProductLine " & strHoodOperator & " ""HOOD"")) "

'DISPLAY REPORT
'===================
'Open report in Design View to make a couple of changes
Application.Echo False, "Preparing Report..." 'do not show work being done; lock screen
'Set BeginningDate and EndingDate controls in report to show user-entered values.
DoCmd.OpenReport strReportName, acViewDesign
DoCmd.Maximize
Reports(strReportName).Filter = strWHERE
Reports(strReportName).FilterOn = True 'have to turn on filter after creating it.
Reports(strReportName)![BeginningDate].ControlSource = "=""" & dteStart & """"
Reports(strReportName)![EndingDate].ControlSource = "=""" & dteEnd & """"
'Save changes so the user won't be asked to; this code always edits before use, anyway!
DoCmd.Save acReport, strReportName
DoCmd.Close acReport, strReportName 'close so that user can't open into design view

'OPEN REPORT for user
DoCmd.OpenReport strReportName, acViewPreview
Application.Echo True, "" 'unlock screen; let user see again

'CREATE ORDER FILE (if blnCreateCustomerOrderFile = True)
'===================
If (blnCreateCustomerOrderFile = True) Then
MsgBox "About to create Order File...", vbInformation, "Customer Service Report"

intFileNum = FreeFile
Open strOrderFile For Output As #intFileNum

'This is the SQL statement used to select the fields that will be included in
'the output file.
'***********************************************************
strSQL = _
"SELECT #" & dteStart & "# AS Expr1, " & _
"CustomerService.[Customer Number], CustomerService.[Item Number], " & _
"IM1_InventoryMasterfile.ProductLine, IM1_InventoryMasterfile.ItemDescription, IMB_PriceCode.DiscountMarkupPriceRate1, CustomerService.[Price Level], " & _
"CustomerService.Quantity, IM1_InventoryMasterfile.TaxClass, IM1_InventoryMasterfile.SalesUM, CustomerService.ID, " & _
"CustomerService.[Next Date], CustomerService.[Frequency] " & _
"FROM ((AR1_CustomerMaster RIGHT JOIN CustomerService ON AR1_CustomerMaster.CustomerNumber=CustomerService.[Customer Number]) " & _
"INNER JOIN IMB_PriceCode ON (CustomerService.[Price Level]=IMB_PriceCode.ItemCustomerPriceLevel) AND (CustomerService.[Item Number]=IMB_PriceCode.ItemNumber)) " & _
"LEFT JOIN IM1_InventoryMasterfile ON CustomerService.[Item Number] = IM1_InventoryMasterfile.ItemNumber " & _
"WHERE ((AR1_CustomerMaster.CustomerNumber <> "" "") AND " & strWHERE & ") " & _
"ORDER BY AR1_CustomerMaster.CustomerNumber, IM1_InventoryMasterfile.ProductLine " & _
";"

'***********************************************************

Set rst = CurrentDb.OpenRecordset(strSQL)

'Print all fields of all records in rst
intSecondLast = rst.Fields.Count - 2 'index of fields collection starts w/ 0
Do While Not rst.EOF
'Print first through second-last fields to current line in file
For intLoop = 0 To intSecondLast
Print #intFileNum, rst.Fields(intLoop).Value & ",";
Next intLoop
'Print last field, and go to next line in file (no semi-colon used at the end)
Print #intFileNum, rst.Fields(intLoop).Value 'Next already incremented intLoop

rst.MoveNext
Loop

Close intFileNum
Set rst = Nothing
MsgBox "Order File has been written at:" & vbCr & strOrderFile, _
vbInformation, "Customer Service Report"
End If
End Function
Thank you again.

 

[CaiSupport]

Thank you so much. Using the query you helped me create as a guide, I managed to change the code and it is working. The correct prices are coming through.

Thank you again so much for your time and patience, I really appreciate it.