Need help w/ code. Want to add a WHERE, but does not work 1

[mvital]

Hi All,

I have the following code I am wanting to add a WHERE clause and I have tried adding it almost everywhere, but it does not work. I don't get an error, but don't get what I want.

Private Sub cmdViewSources_Click()
wherestring = vbNullString
If IsNull(txtSourceKeyword) Then

FormIndex.RowSource = "SELECT tbl_material_description.[HOMOG_MATERIAL_ID], tbl_material_description.[BUILDING_ID] FROM tbl_material_description ORDER BY tbl_material_description.[HOMOG_MATERIAL_ID];"

Else
strWildcard = txtSourceKeyword

wherestring = "WHERE(((tbl_material_description.[HOMOG_MATERIAL_ID]) like ""*" & strWildcard & "*""))"
FormIndex.RowSource = "SELECT tbl_material_description.[HOMOG_MATERIAL_ID], tbl_material_description.[BUILDING_ID] FROM tbl_material_description " & wherestring & " ORDER BY tbl_material_description.[HOMOG_MATERIAL_ID];"

End If
End Sub

I have a list box and it gives me the results I want. Basically, the materials per building. Now when I do a search on the list box with or without wildcards I want it to also give me the materials for just one building. Right now when I click on the cmd button (code above). I get all the materials for all the buildings.

The record source for the list box is the following and again it gives me what I want; SELECT tbl_material_description.HOMOG_MATERIAL_ID, tbl_material_description.BUILDING_ID FROM tbl_material_description WHERE tbl_material_description.BUILDING_ID=forms!frm_main_data_entry.building_id ORDER BY tbl_material_description.HOMOG_MATERIAL_ID;

So, I want to add the WHERE above to the cmd button code so it too gives me the materials for the particular building I am on.

Hope it makes sense.

thanks a BUNCH!

 

[Eupher]

Hiya, mvital,

How about this:

strWildcard = "*" & txtSourceKeyword & "*"
    
    wherestring = "WHERE(((tbl_material_description.[HOMOG_MATERIAL_ID]) like [highlight]'[/highlight]" & strWildcard & "[highlight]'[/highlight]))"

You need to delimit text variables with single quotes in VBA, as highlighted above.

HTH,

Ken S.

 

[mvital]

Thank you Ken. I have it working, but it does not give me only the materials for a particular building. I want to incorporate in this WHERE clause :
wherestring = "WHERE(((tbl_material_description.[HOMOG_MATERIAL_ID]) like '" & strWildcard & "'))"


this WHERE clause:

WHERE tbl_material_description.BUILDING_ID=forms!frm_main_data_entry.building_id

Can't get the syntax just right so that it says the first where and where sencond where??

Any ideas?

 

[Eupher]

How about an AND clause?

strWildcard = "*" & txtSourceKeyword & "*"
    
    wherestring = "WHERE (tbl_material_description.[HOMOG_MATERIAL_ID] like '" & strWildcard & "') " _
    & "AND (tbl_material_description.BUILDING_ID = " & forms!frm_main_data_entry!building_id & "))"

This assumes building_id is numeric data. If it is text, you will need to delimit the form/field reference with single quotes as we did with strWildcard.

HTH,

Ken S.

 

[mvital]

It did not work this time it gave me no results. Now the HOMOG MATERIAL ID is text but has '-' in it i.e. BBA-01. I seem to remember a hyphen is a special key so I need to refer to it differently.

Thanks for continuing to help!!!

 

[Eupher]

mvital,

You say that this where string by itself returns records, but adding the AND clause returns nothing?

wherestring = "WHERE(((tbl_material_description.[HOMOG_MATERIAL_ID]) like '" & strWildcard & "'))"

If that is so, then there is no problem with the hyphen. The hyphen does also serve as the subtraction operator, but as far as I know, as long as the hyphen is within a text string there is no problem. What *does* cause problems is when the standard delimiters - single quotes, double quotes, pound sign - are used improperly in text strings.

You did not address one item in my previous post: is building_id numeric or text? The answer will affect the syntax.

Ken S.

 

[mvital]

Thanks again Ken. The building_id is text; alpha numeric info can be captured. I did add the single quotes and it still did not work.

 

[Eupher]

Can you post the entire SQL string?

Ken S.

 

[TheAceMan1]

How are ya mvital . . .

[blue] If [purple]IsNull[/purple](txtSourceKeyword) Then[/blue]

A string can't be [blue]Null![/blue] It can be a [blue]Null String[/blue] or [purple]""[/purple]. So IsNull fails if [blue]txtSourceKeyword[/blue] is empty!

Try this:

[blue]Private Sub cmdViewSources_Click()
   Dim SQL As String, Cri As String, strWildcard As String
   Dim prp As Property, ID As String, DQ As String
   
   Set prp = FormIndex.Properties("RowSource")
   DQ = """"
   SQL = "SELECT TMD.[HOMOG_MATERIAL_ID], TMD.[BUILDING_ID] " & _
         "FROM tbl_material_description AS TMD "
   
   If Trim(txtSourceKeyword & "") <> "" Then
      ID = DQ & Forms!frm_main_data_entry!building_id & DQ
      strWildcard = DQ & "*" & strWildcard & "*" & DQ
      Cri = "WHERE ((TMD.building_id = " & ID & ") AND " & _
                   "(TMD.[HOMOG_MATERIAL_ID] LIKE " & strWildcard & ")) "
      SQL = SQL & Cri
   End If
   
   prp = SQL & "ORDER BY TMD.[HOMOG_MATERIAL_ID];"
   
   Set prp = Nothing

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[mvital]

Thanks Ken and "TheAceMan1".

I tried your code TheAceMan1 and I broken down for my benefit to make sure I understand what it is doing; I am new to programming, but learning a lot from this group and by having to do it on my own.

I tested the code; I put some breaks and step into the code. I searched for homog_material_id = BBA* for the BUILDING_ID = Garage. When I clicked on the cmd button it did nothing. I can see all the results for the building id I requested. I want to go one step further and if I search for BBA*; I want to see the materials for garage that start with BBA*.

Here's what I found when I tested.

prp = to the SQL - OK
DQ = """"
SQL = ""
If Trim txtSourceKeyword = "BBA*" - OK
ID = ""
strWildcard = ""
Cri = ""
SQL = SQL & Cri = ""
prp = SQL code

So why all the "" from the ID part of the code?

Thank you both for all your help!

The Garage has the following materials:
BBA-01
CB-01
DW-01

When the txtSourceKeyword = "BBA*"
The list box (FormIndex) should only show me "BBA-01". With the code above I see BBA-01, CB-01, and DW-01.

 

[TheAceMan1]

mvital . . .

[blue][purple][b]Change:[/b][/purple]
      strWildcard = DQ & "*" & strWildcard & "*" & DQ
[purple][b]To:[/b][/purple]
      strWildcard = DQ & [purple][b]txtSourceKeyword[/b][/purple] & DQ[/blue]

This change removes wildcards appended by code leaving you to include them in [purple]txtSourceKeyword[/purple].

Next add the following Debug.Print line where you see it:

[blue]   prp = SQL & "ORDER BY TMD.[HOMOG_MATERIAL_ID];"
   [purple][b]Debug.Print prp[/b][/purple][/blue]

Set a breakpoint on the line following Debug.Print, run the code and post back the results from the immediate window . . .

See Ya! . . . . . .

 

[mvital]

Hi TheAceMan1

Thank you SO much for continuing to work this through with me.

SELECT TMD.[HOMOG_MATERIAL_ID], TMD.[BUILDING_ID] FROM tbl_material_description AS TMD WHERE ((TMD.building_id = "Garage") AND (TMD.[HOMOG_MATERIAL_ID] LIKE "bb*")) ORDER BY TMD.[HOMOG_MATERIAL_ID];

I took the breakpoint and it worked!!! Yeah!! Yeah!! You just made my weekend! Thanks a bunch!

 

[TheAceMan1]

mvital . . .

Don't forget to remove the [blue]Debug.Print[/blue] line. Its just for troubleshooting which I'm sure you'll make great use of in the future . . .

See Ya! . . . . . .

 

[mvital]

I did TheAceMan1. Thanks again!