• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Query Form

Experts,

I am making this query form.
I have 2 unbound text boxes txtCoName, txtLCNo.
A user is to enter either a Co Name or and LC No and hit button and form opens with criteria.
The form with the results is frmLCissued_TradeChannel

I know this is a little tricky.  I did get this idea from an expert on this site awhile ago but something is missing.

Th e criteria I am after:
If the txtCoName is null and the txtLCNo is populated I want the form to open to those parameters.  But if the LCNo is null and the txtCoName is populated I need the form to open to those parameters.  

I thought I had it correct in the below SQL but seems like something is missing.
Do you see it?
 (The pic might make more sense)


SELECT tblEndUser.[End User], tblLetterOfCredit.LCNo, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.Amount, tblLetterOfCredit.LCtype
FROM tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID

WHERE (((tblEndUser.[End User]) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName],"") & "*") AND ((tblLetterOfCredit.LCNo) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo],"") & "*"))
OR (((tblEndUser.[End User]) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName],"") & "*" And (tblEndUser.[End User])<>"false") AND ((tblLetterOfCredit.LCNo) Like "*" & Nz([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo],"") & "*"
And (tblLetterOfCredit.LCNo)<>"false")
AND ((IsNull([forms]![frmLCIssued_TradeChannel_Choose]![txtLCNo]))=True)
AND ((IsNull([forms]![frmLCIssued_TradeChannel_Choose]![txtCoName]))=True));
 screen shot 1 of 2untitled.JPG
0
pdvsa
Asked:
pdvsa
  • 11
  • 10
1 Solution
 
Dale FyeCommented:
Is this the query that returns the recordset for frmLCissued_TradeChannel?

If so, leave those criteria out of the forms recordset.  Instead, use a command button on the first form, to set the criteria, something like:

Private Sub cmd_LCIssued_TradChannel_Click

    Dim strCriteria as string

    if Len(me.txtCoName & "") > 0 then
        strCriteria = "tblEndUser.[End User] = " & chr$(34) & me.txtCoName & chr$(34)
    endif

    if len(me.txtLCNo & "") > 0 then
        if LEN(strCriteria) > 0 then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is numeric, not text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & me.txtLCNo
    endif

    Docmd.Openform "frmLCissued_TradeChannel",,,strCriteria

End Sub
0
 
pdvsaProject financeAuthor Commented:
ahhh...that sounds like a great idea.  

Let me do that...

thank you
0
 
pdvsaProject financeAuthor Commented:
fyed, I have one small request.  The LCNo is actually text and not numeric.  I have letters in the LCno as well as numbers so I have to have it as text.

How to modify your code?

much appreciated sir...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Dale FyeCommented:
You need to wrap the value of the LCNo textbox in quotes, similar to the way I did with txtCoName

strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & chr$(34) & me.txtLCNo & chr$(34)
0
 
Dale FyeCommented:
Personally, I actually use a function (fnWrap) to add the quotes (single or double), or even # for dates to strings.  
Public Function fnWrap(WrapWhat As Variant, Optional WrapWith As Variant = """") As String

    fnWrap = WrapWith _
           & IIf(Nz(WrapWith, "") = Chr$(34) Or Nz(WrapWith, "") = Chr$(39), _
                 Replace(WrapWhat, Nz(WrapWith, ""), Nz(WrapWith, "") & Nz(WrapWith, "")), _
                 WrapWhat) _
           & WrapWith
    
End Function

Open in new window


This accepts a value (WrapWhat) and a parameter which defines what character(s) to wrap around the value that was passed.  The WrapWhat is defined as a variant to allow you to pass a null value and get that value wrapped in the appropriate delimiters.  One of the nice things about this function is that if WrapWhat contains characters like the WrapWith characters (single or double quotes) it will actually replace those values in the text with two of that same character, which is recognized by most databases as a single instance.

In your case, I would use it like:

strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & fnWrap(me.txtLCNo)

With a date field, I would do something like:

strCriteria = strCriteria & "[DateField] = " & fnWrap(me.txtStartDate, "#")

0
 
pdvsaProject financeAuthor Commented:
fyed:
I have to use a different SQL now.  I thought that it would not make a difference.  
When I click the button to open the form, I do not get any results.  

With this new SQL of the form that is opening with the results (shown below), do you see any issues?  I do not see any.  The tables referenced are the same. The unbound text boxes names are correct too.


SELECT tblEndUser.[End User], tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType, tblLetterOfCredit.Comments

FROM (tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
0
 
Dale FyeCommented:
First thing I would do is verify that the SQL actually returns records, when not linked to the form.

I hadn't thought about it, but in the Click event, when you are building the strCriteria, you need to remove any reference to the table that the fields are coming from.  So it should look more like:

Private Sub cmd_LCIssued_TradChannel_Click

    Dim strCriteria as string

    if Len(me.txtCoName & "") > 0 then
        strCriteria = "[End User] = " & chr$(34) & me.txtCoName & chr$(34)
    endif

    if len(me.txtLCNo & "") > 0 then
        if LEN(strCriteria) > 0 then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is numeric, not text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & chr$(34) & me.txtLCNo & chr$(34)
    endif

    Docmd.Openform "frmLCissued_TradeChannel",,,strCriteria

End Sub

Open in new window


Also, in your original query you were using Like in the query.  I was unsure whether this was intentional because you wanted to be able to enter part of the Co Name, or whether it was because you wanted to return all records for that field in the associated textbox was empty (but I assumed it was the latter).
0
 
pdvsaProject financeAuthor Commented:
fyed:
<First thing I would do is verify that the SQL actually returns records, when not linked to the form.
It does return records. I tested this.  The text i have in the unbound fields is actual data in the table too.  

I still am not getting any results though.  
below is how I have it now.  
Only change is the form that is opening.  I did change that.  
I tried it with and witout the tblLetterOfCredit (bolded below) as you did remove the tblEndUser.

Any other suggestions?  The names are correct on the unbound fields.  

Private Sub cmdOpenRpt_Click()
   
    Dim strCriteria As String
 
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
 
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "tblLetterOfCredit.[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
 
    DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
 


End Sub


here is the sql again (it is the same from above though) from the form that is opening to the criteria on the query form.

SELECT tblEndUser.[End User], tblLetterOfCredit.ProjectID, tblLetterOfCredit.Amount, tblLetterOfCredit.LCNo, tblLetterOfCredit.LCType, tblLetterOfCredit.Currency, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.DateOfIssueSB, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.EndUserID, tblLetterOfCredit.ValidUntil, tblLetterOfCredit.CSMNo, tblBanks_Participating.BankID, tblBanks_Participating.BankType, tblLetterOfCredit.Comments

FROM (tblLetterOfCredit INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID;
0
 
Dale FyeCommented:
Oops,  you were right to remove the "tblLetterOfCredit." refernece.

The next thing I would do is:

1.  Make sure the forms "filterOnLoad" property in the Data tab of the properties dialog is set to no.
2.  Use the following code, it adds a debug.print to print the value of strCriteria to the debug window, and also opens the form without the criteria.

 
Private Sub cmdOpenRpt_Click()
    
    Dim strCriteria As String
  
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
  
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If

    debug.print strCriteria
    'DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient"

End Sub

Open in new window


If that opens the form with data, then in the immediate window, type:

forms!frmLCIssued_toClient.filter = "paste the strCriteria here"
forms!frmLCIssued_toClient.filterON = true

NOTE: on the filter line above, make sure you use the criteria string that the debug command printed.

If that doesn't work there may be a problem with strCriteria.  Post that string back in the next post.
0
 
pdvsaProject financeAuthor Commented:


forms!frmLCIssued_toClient.filter = "paste the strCriteria here"
forms!frmLCIssued_toClient.filterON = true

Please see pic.  I closed and reopened teh form with that Immediate window info and it gave me an syntax error 'Missing Operator' in 'gs cal'  upon opening of the form.

You can see the other strcriteria there that teh system added based on what I input in the query form.

What do you think now?
 Immediate Windowuntitled.JPG
0
 
pdvsaProject financeAuthor Commented:
did not completly finish that

<If that opens the form with data, then in the immediate window, type:
it does open but to all data and not based on the filter.

the filter on load is turned off.  
0
 
Dale FyeCommented:
Try:

forms!frmLCIssued_toClient.filter = "[End User] = 'gs cal'"
forms!frmLC_Issued
0
 
pdvsaProject financeAuthor Commented:
dangit...still get syntax error on End User
here is copy past of immediate window.  
What next?  
I iknow it is difficult if you cant see the db.

[End User] = "gs cal"
[LCNo] = "ctcs"
[LCNo] = "ctcs"
forms!frmLCIssued_toClient.filter = "[End User] = 'gs cal'"
forms!frmLCIssued_toClient.filterON = true
0
 
Dale FyeCommented:
Are you saying that a single debug.print strCriteria is returning three rows, like:

[End User] = "gs cal"
[LCNo] = "ctcs"
[LCNo] = "ctcs"

You did say that the query runs fine, and that the form opens (unfiltered) properly, correct?

Can you copy and paste the current code you are using.
0
 
pdvsaProject financeAuthor Commented:
I think I had some corruption.

I get this in the immediate window now:

[End User] = "gs cal"  and this is what is in the query form.

here is the button code:
Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
   
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] = " & Chr$(34) & Me.txtCoName & Chr$(34)
    End If
   
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
 
    Debug.Print strCriteria
    'DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient"
 
End Sub
0
 
pdvsaProject financeAuthor Commented:
I still do not have any results in the data form but that record "GS Cal" is in the data set under End User.
0
 
Dale FyeCommented:
is the entry in the recordset exactly "GS Cal" or does it have preceding or following characters.

Try:

forms!frmLCIssued_toClient.filter = "[End User] Like '*gs cal*'"

do you have an Option Compare statement at the top of your code module? If so, what does it say?

Most people use:

Option Compare Database
0
 
pdvsaProject financeAuthor Commented:
fyed:  
<is the entry in the recordset exactly "GS Cal" or does it have preceding or following characters.
It does not have any preceding characters but does have some after.  It is definitely a record though.  

I do have option compare in both forms (filter and form opening).

I figure it is something that  has been overlooked.  I appreciate you sticking with me on this because I really want this to work. I have been looking for a "clean" way to use a filter form with >1 unbound field.  

Maybe if you see the db  it will be more clear.
I have ul it and the filter form opens auto.  

Please take a crack at it when you get a sec.  I checked the names and all and i dont believe it is something simple like that.  I hope it is not something stupid I did ;) but that is very possible.  

Please type in "GS Cal" for GS Caltex and I believe 51 records appear which is all the records but for GS Cal it should be only 2 or so.  

thanks for spending the time...really appreciate the expert help.

DatabaseNew.accdb
0
 
Dale FyeCommented:
OK< as I suspected, you really did want to use the LIKE operator in your criteria string for the [End User] field.  So, try the following code; it worked for me.
Private Sub cmdOpenRpt_Click()

    Dim strCriteria As String
    
    If Len(Me.txtCoName & "") > 0 Then
        strCriteria = "[End User] Like " & Chr$(34) & "*" & Me.txtCoName & "*" & Chr$(34)
    End If
    
    If Len(Me.txtLCNo & "") > 0 Then
        If Len(strCriteria) > 0 Then strCriteria = strCriteria & " AND "
        'This assumes that the LCNo is text
        strCriteria = strCriteria & "[LCNo] = " & Chr$(34) & Me.txtLCNo & Chr$(34)
    End If
  
    Debug.Print strCriteria
    DoCmd.OpenForm "frmLCIssued_toClient", , , strCriteria
  
End Sub

Open in new window


This will actually filter on records that contain "gs cal" anywhere in the [End User] field.  If you want it to only select those records where "gs cal" appears at the beginning of the field, then use:

strCriteria = "[End User] Like " & Chr$(34) & Me.txtCoName & "*" & Chr$(34)
0
 
pdvsaProject financeAuthor Commented:
yessir that was it!   I was thinking that the LIKE was in the code but I did not read it very closely.  

thank you for helping me.  I would give you more pts if I could.

pdvsa
0
 
Dale FyeCommented:
I'm just glad we got it figured out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now