Help with a where condition

[David Desormeau]

Hi everyone,

I need help with a database i'm working on. It is almost fully fonctionnal. I have a form that is a menu and this form contains command buttons. Most of them work, but I cannot get one to do what I want. I need to be able to click on the button and when I do it ask me a number (3 or 4 characters). It then displays a report with the contract numbers ending with this number I just entered. I tried entering a where condition with a like "*[Lot]" ([Lot] being the name of that field with the 3 or 4 characters), but this does not work. I have other buttons that work almost like this one, but I enter the whole contract number so it works fine. The problem is really when I need to find all the contracts ending with the same numbers.

Right now my where condition in access for the bouton is : Report![RapportProjet]![Contrat] like "*[Lot]" (my database is french so the names of the fields and tables are french) Lot is a text box in the report so when I open it, it asks me what value I want. I enter 104B (for example). I then want all of the entries with a contract number ([contrat]) ending with 104B to appear in the report and not the other entries.

I hope you understand my problem and will be able to help me.

David

[NeoPa]

Where are you putting the WHERE condition David?

I would expect this to be a filter applied to the opening of the report. In that case the left-side of the comparison should be unqualified ([Contrat]) whereas the right-side should be qualified and outside of the quotes. I have no reference to your form but it might be something like :

"[Contrat] Like '*' & Forms!YourForm.Lot"

If this is always triggered on the fly then an easier approach would be to set up the string in your VBA using the literal value :

strFilter = "[Contrat] Like '*" & Forms!YourForm.Lot & "'"

[David Desormeau]

I put the condition in the macro window (see image). Of course I'm french so the window might seem a little different than yours because of the text but the code I entered is what I wrote in my first post. For my other buttons (the ones that work) I wrote: [TableDeficience s]![Contrat]=[Forms]![FormulaireDefic iences]![Contrat]![Reports]![PetitRapport]![Étiquette27]

This then asks me what contract I want from my table and from the form and displays only these in the report. HoweverI cannot seem to get it to work when I just want the last numers of the contract and not the whole thing.

[Mihail]

I think that you make a little mistake:
The WHERE condition must be a string, isn't it ?
So, "*[Lot]" IS a string (with the characters as you see: *[LOT].

Try that:
Declare a STRING variable (Say S).
Assign to this variable the value: S = "Like *" & [LOT].
Use this variable as your WHERE condition.

Good luck !

[David Desormeau]

Ok, I'm guessing this must be in the Visual Basic code so I remved it from the where and wrote this in the onClick event of the button : However, an error message pops up (error 2465) and it does not seem to work. Am I doing something wrong? The [Lot] is not in any tables. I just added it in the report so it asks which value I want when I open it and displays it on the report. Could this be the probem? I really appreciate your help by the way.

David

[NeoPa]

David, we have code windows for displaying code. Yours is legible, but unusual. Code windows work better in here.

Private Sub Command64_Click()

    Dim strSQL As String
    strSQL = "[Contrat] Like '*" & Me.Lot & "'"
    Call DoCmd.OpenReport(RapportProjet, acViewDesign, , strSQL, acWindowNormal)

End Sub

This is what I was saying in post #2, but I didn't have the context then to narrow it down quite this far (Me.Lot only works when you are running the code from the same form that [Lot] is a control of).

[David Desormeau]

I tried your code and the problem is with the Me.Lot. Lot is not in any of the tables I made and it is not it the form either. I used a text box in the report and named it Lot. When I try to open the report, it asks me which Lot number I want. How do I make the code work in this case?

[Rabbit]

If you don't have the Lot field in any of your tables, how exactly did you plan on filtering by it? An object must exist for an action to be performed on it.

[NeoPa]

David, This is all about the lack of important information that you should have included in the question. I read your post #5 and, although it didn't explain where [Lot] came from, it did rather imply it was on the local form (How else would your reference make any sense). What you explain doesn't. I expect that's because you're a little confused about how the filtering works.

Basically, the filter is applied to the record source of the report. The assumption I was working to was that [Contrat] is a field in that recordset. The filter works by saying :
"Show only the records where the field [Contrat] matches certain specified criteria."

I assume you can see how that would make little sense if the criteria were comparing it with something that cannot be determined until after the filtering has been applied. Frankly, although you haven't explained your data structure very fully, I don't expect there are many situations where it even makes sense to compare one field in a record with another (although that is at least feasible), as you appear to want to do.

Why don't you explain what it is you are trying to achieve and we can try again actually knowing what the situation is. That may help.

[Mihail]

I think (yet) that is a syntax error. "Lot" it is an anbound text box. So [Lot] is an incorrect syntax. Retry NeoPa's code but replace (or add) Me.Lot with Me.Lot.Text (or, simply, Lot.Text).

[David Desormeau]

Ok. What I'm trying to achieve is that when I click on a button in a form I made called FrmMenu (there are only buttons in this form. No data entry) I want it to ask the user what lot number he wants. Then with that number, it finds all the contracts from the table and displays only the ones ending with the lot number specified (because the contracts have the form XX-X-XXX and the last 3 X are the lot number). I also want to have the lot number the entered appear in the report header and that is why I put a text box with a tag up there named lot. So when I would open the report it would ask me a lot number because it cannot find any in the tables (because it isn't in any table). I'm pretty sure there is an easier way of doing this, but I'm pretty new to VBA coding and ok with sql and other database stuff.

[Mihail]

Take a look here:

http://bytes.com/topic/access/answers/913376-how-give-value-textbox-query

and here:

http://bytes.com/topic/access/answers/913577-set-query-criteria-based-control-values

I am pretty sure that you find the answer.
Remember: To open an report, Access must evaluate the query. So the QUERY must know the "Lot" value, not the report after.

[NeoPa]

David, you need a TextBox control on your form (or one of your forms).

If you really don't want the [Lot] box on your menu form then get the command button to open a separate form specially for this report. That seems somewhat clumsier to me (than having it on FrmMenu), but it's your project, so your choice.

Ultimately, whichever form it is on should have the code that opens the report within it. The code will look very much like the code I posted for you earlier in post #6. I can't guarantee the Command Button will have the same name as before (very unlikely in fact) but otherwise it's all there.

Bonne chance!