By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

Open Form using Multiple Criteria

P: 6
i have constructed database to display specific records according to user choice from multiple comboboxes based on one taBLE but tried with many ways and give up
can any one help me to correct the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub search_Click()
  2. On Error GoTo Err_search_Click
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  7.      Dim stLinkCriteria1 As String
  8.       Dim stLinkCriteria2 As String
  9.        Dim stLinkCriteria3 As String
  11.     stLinkCriteria1 = " central =  " & Me.comcentral & ""
  12.     stLinkCriteria2 = " AND from = '  " & "'" & Me![comfrom] & "'"
  13.     stLinkCriteria3 = " AND cto = '  " & "'" & Me![comto] & "'"
  14.    stLinkCriteria = stLinkCriteria1 & stLinkCriteria2
  15.    stLinkCriteria = stLinkCriteria & stLinkCriteria3
  17.   MsgBox (stLinkCriteria)
  19.     stDocName = "kpi"
  20.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  22. Exit_search_Click:
  23.     Exit Sub
  25. Err_search_Click:
  26.     MsgBox Err.Description
  27.     Resume Exit_search_Click
  29. End Sub
i tried to filter the form too but it doesn't work
plz help
Feb 22 '14 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,240
Welcome to Bytes. You'll find a lot of people here willing and able to help, but you have to do your best to explain your problem.

You are not telling us what is happening. "It doesn't work" is not really much to go on.

That being said, I suspect that 'from' and 'to' indicate you really want to test for a range and not equality. from= and cto= is a little curious to me. I would expect a from / to pair to use a BETWEEN verb in the criteria. Something like
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria3 = " AND cto BETWEEN '" & Me![comfrom] & "' AND " '"  & Me![comto] & "'"
Also, using code tags is both helpful and required on the site. Click on the [code] tag and then type your lines of code between the tags that appear in your post.

Feb 22 '14 #2

P: 6
thank you jim for replying
i'm sorry if i wasn't so clear but this is the first time i post a question
the data base has one table with columns "from as begining date , to as ending date, central , name and code" user is supposed to choose to search about one of the columns (code, name or central" and choose the beginning and the end of search date "they are different columns so i think i can't use between"
the problem is the form opened empty
thanks again
Feb 22 '14 #3

Expert 5K+
P: 8,701
Assuming neither of the three Fields can be NULL, the Criteria String should look something like the following. I am still a little confused on what you are looking for, so the below illustration is just a generalization:
Expand|Select|Wrap|Line Numbers
  1. Dim strLinkCriteria As String
  3. stLinkCriteria = "[central] = '" & Me.[comcentral] & "' AND [Date Field] BETWEEN #" & _
  4.                   Me![comfrom] & "# AND #" & Me![comto] & "#"
  6. DoCmd.OpenForm stDocName, , , stLinkCriteria
Feb 22 '14 #4

Expert 100+
P: 1,240
What might be very helpful to you is to make a query with the Access query editor that returns a sample of data like you want. Just hard code the criteria, for the sake of the example, by keying typical values into the criteria line of the query editor.

Once you have that, you can actually see the SQL code that results from your query. Right-click on the upper panel of the query editor and choose the SQL view. Look at what follows the Where verb in the SQL. That right there is what you want your criteria to look like (without the 'where' verb)

This will teach you a whole lot of useful Access stuff. Stick with til you get it. We're here to help when you get stuck.

Feb 22 '14 #5

P: 6
thank you all
the data base is a performance report for a company , the user should be able to search the required field either the code of employee , his name or the central name but for any field he is supposed to set the from date and to date to start the search "from and to are 2 different columns" and the resulting form should display all records that satisfy the 3 chosen conditions "from, to and one of the other fields"
when i tried the code the stLinkCriteria value is "false" why is that?
thank you again
Feb 22 '14 #6

P: 6
thank you all again i did it with a query then copy and paste the code from sql view
Feb 23 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.