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

Query by form

P: 6
Hi, I'm a relative beginner to Access, and have just found out how to construct a 'query by form' using the SQL, Select/From/Where approach. With the addition of a few wildcards, I've got a form with a couple of text boxes, requiring just a letter or so, to pull out 'like' information. So in order to do this, I have an 'and' statement, linking the two expressions relating to each field (which in turn have 'or' statements linking the wildcard / is null expressions).

The next thing I've been trying to do, is to add a couple more text boxes as 'start' and 'end' dates relative to a date field in the dbase. However, when I add another 'and' statement to the above expressions (which has internal 'and / or' statements relating to a 'between' start and end date expression) I get prompted to add the start / end dates. This prompt is both when I open the form, and when I try to run the query.

For some reason, the query doesn't seem to be reading the information I put in the 'start and end' date text boxes. I've been wondering if there is a limit to the number of 'and' statements linking expressions in the 'where' area.

Any suggestions?

regards
stu
Jun 11 '07 #1
Share this Question
Share on Google+
11 Replies


theaybaras
P: 52
Can you post the relevant code/query information, that will give us something to check through to look for problems.

theAybaras
Jun 11 '07 #2

P: 6
Hi and thanks for the quick response: Here goes:

SELECT [Hours worked].Hoursworked,[Hours worked].date,Employees.EmployeeName,Suppliers.SupplierNam e,Employees.NonManual
FROM ([Hours worked] INNER JOIN Employees ON [Hours worked].Employee = Employees.EmployeeNumber) INNER JOIN Suppliers ON Employees.Company = Suppliers.SupplierCode
WHERE ((Employees.EmployeeName) like "*" & Forms!frmQBFDraft2!Text0 & "*" or Forms!frmQBFDraft2!Text0 is Null) and ((Suppliers.SupplierName) like "*" & Forms!frmQBFDraft2!Text2 & "*" or Forms!frmQBFDraft2!Text2 is Null) and ((([Hours worked].Date) between Forms!QBFDraft2!startdate and Forms!QBFdraft2!enddate)or Forms!QBFdraft2!startdate is null);

As I said in my earlier note, I get prompted for the dates when I open the form, and when I run the query. No notice is taken of any dates I enter into the 'startdate' or 'enddate' fields.

Regards
stu
Jun 12 '07 #3

theaybaras
P: 52
Expand|Select|Wrap|Line Numbers
  1. ((([Hours worked].Date) between Forms!QBFDraft2!startdate and Forms!QBFdraft2!enddate)or Forms!QBFdraft2!startdate is null)
  2.  
stu

I need to take a deeper look, but on a cursory glance you could try putting brackets around your startdate and enddate form fields... i.e. the code above would look like
Expand|Select|Wrap|Line Numbers
  1. ((([Hours worked].Date) between Forms!QBFDraft2![startdate] and Forms!QBFdraft2![enddate])or Forms!QBFdraft2![startdate] is null)
  2.  
I don't know that that will work, but it is the format I usually use when calling a field from the Forms! Collection.

later

theAybaras
Jun 12 '07 #4

P: 6
Hi

Putting the brackets around the 'startdate' / 'enddate' didn't make any change. In fact, when I went back in to look at the query, the brackets weren't there any longer. Made the changes again, saved everything, but still same results.

Interested in any other suggestions you might have.

cheers
stu
Jun 13 '07 #5

theaybaras
P: 52
Hi stu,

I didn't think the brackets would really help, but I was hoping. Also, I noticed there is a space in the middle of a word in your query... not sure if that is an error in your query or only in the post above. I should have time to look over the next day or so, but I don't know... I have had some major life interruptions.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Hours worked].Hoursworked,[Hours worked].date,Employees.EmployeeName,Suppliers.SupplierNam e, Employees.NonManual
  2.  
the Suppliers.SupplierName (Nam e) has a space in the above code, is that the case in your actual sql?

Maybe one of the real experts has a suggestion.. I certainly don't claim that title. :)


thanks for your patience

theAybaras
Jun 13 '07 #6

P: 6
Hi

I hope the major interruptions that you talk about are not too daunting for you. Please don't worry about this little problem at all. Have really appreciated your input thus far.

For your interest, I've copied the query below (copy and paste this time). Can't see any spaces in the words.

regards
stu

SELECT [hours worked].Hoursworked, [hours worked].Date, Employees.EmployeeName, Suppliers.SupplierName, Employees.NonManual
FROM ([hours worked] INNER JOIN Employees ON [hours worked].Employee=Employees.EmployeeNumber) INNER JOIN Suppliers ON Employees.Company=Suppliers.SupplierCode
WHERE ((Employees.employeename) Like "*" & Forms!frmQBFDraft2!Text0 & "*" Or Forms!frmQBFDraft2!Text0 Is Null) And ((Suppliers.Suppliername) Like "*" & Forms!frmQBFDraft2!Text2 & "*" Or Forms!frmQBFDraft2!Text2 Is Null) And ((([hours worked].Date) Between Forms!QBFDraft2!startdate And Forms!QBFdraft2!enddate) Or Forms!QBFdraft2!startdate Is Null);
Jun 13 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Hi

I hope the major interruptions that you talk about are not too daunting for you. Please don't worry about this little problem at all. Have really appreciated your input thus far.

For your interest, I've copied the query below (copy and paste this time). Can't see any spaces in the words.

regards
stu

SELECT [hours worked].Hoursworked, [hours worked].Date, Employees.EmployeeName, Suppliers.SupplierName, Employees.NonManual
FROM ([hours worked] INNER JOIN Employees ON [hours worked].Employee=Employees.EmployeeNumber) INNER JOIN Suppliers ON Employees.Company=Suppliers.SupplierCode
WHERE ((Employees.employeename) Like "*" & Forms!frmQBFDraft2!Text0 & "*" Or Forms!frmQBFDraft2!Text0 Is Null) And ((Suppliers.Suppliername) Like "*" & Forms!frmQBFDraft2!Text2 & "*" Or Forms!frmQBFDraft2!Text2 Is Null) And ((([hours worked].Date) Between Forms!QBFDraft2!startdate And Forms!QBFdraft2!enddate) Or Forms!QBFdraft2!startdate Is Null);

Try changing the WHERE clause to include the use of the CDate function to convert the date strings in the textboxes to date data types for comparison with the date fields in the table. Also, to make sure that the wildcard is interpreted correctly, I wrapped it in the ascii character 34 for double quotes. Hope it helps.

WHERE ((Employees.employeename) Like & Chr(34) & "*" & Chr(34) & Forms!frmQBFDraft2!Text0 & Chr(34) & "*" & Chr(34) & Or Forms!frmQBFDraft2!Text0 Is Null) And ((Suppliers.Suppliername) Like & Chr(34) & "*" & Chr(34) & Forms!frmQBFDraft2!Text2 & Chr(34) &"*" Chr(34) & Or Forms!frmQBFDraft2!Text2 Is Null) And ((([hours worked].Date) Between CDate(Forms!QBFDraft2!startdate) And CDate(Forms!QBFdraft2!enddate)) Or Forms!QBFdraft2!startdate Is Null);
Jun 14 '07 #8

P: 6
Hi

Put the code into the query as shown, but no joy. Played around and made a few changes, but still no luck.

When you use the chr(34) approach, do you take the " out? Seems that chr(34) is the ascii code for ", so you wouldn't use both, would you?

Will try working with the cdate function as well, although I've made both the date format in the text box on the form, and in the query, the same format.

Thanks for the effort so far.

cheers
stu
Jun 16 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
Hi

Put the code into the query as shown, but no joy. Played around and made a few changes, but still no luck.

When you use the chr(34) approach, do you take the " out? Seems that chr(34) is the ascii code for ", so you wouldn't use both, would you?

Will try working with the cdate function as well, although I've made both the date format in the text box on the form, and in the query, the same format.

Thanks for the effort so far.

cheers
stu
Stu,

Regarding Cdate: As I understand it, there is a technical difference between the date data type and date format. Unbound textbox controls on your form can be formatted to display like a date, but anything entered is still a text string, not a numeric date data typeÖ.hence the need to convert an unbound control to a date data type when needed in a numeric expression.

Regarding the Chr(34) and double quotes: The need for both seems to depend on whether or not you have the entire SQL string in quotes. In those cases where you donít (like in your case) and the wildcard is double quoted, it appears that Chr(34) is required in addition to the double quotes to force an insert of double quotes into the SQL string on either side of the search criteria. The only way you can tell for sure if both are needed is when you print out the entire SQL string in the debug window during the debugging process.
Jun 16 '07 #10

P: 6
Hi

I've spent quite a bit of time today playing with this, and have finally got it working, using the construct below. Tried the cdate and Chr(34) options, and got things pretty close, but the breakthrough came when I replaced the 'startdate / enddate' names with the text box names. Startdate and Enddate were the text box captions. I would have thought I would be able to use the caption names?

Anyhow, I might now go back and put in the cdate etc, just to make things a bit more secure, but pretty pleased that things are working. Have wanted to be able to query like this, for ages.

Really appreciate the help. Lots of other things I need to do with a database I'm using, so no doubt we'll talk again.

regards
stu

WHERE ((Employees.EmployeeName) Like "*" & Forms!frmQBFDraft2!Text0 & "*" Or Forms!frmQBFDraft2!Text0 Is Null) And ((Suppliers.SupplierName) Like "*" & Forms!frmQBFDraft2!Text2 & "*" Or Forms!frmQBFDraft2!Text2 Is Null) And ((([hours worked].date) Between Forms!frmQBFDraft2!text6 And Forms!frmQBFDraft2!text8) Or (Forms!frmQBFDraft2!text6 Is Null));
Jun 17 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
Hi

I've spent quite a bit of time today playing with this, and have finally got it working, using the construct below. Tried the cdate and Chr(34) options, and got things pretty close, but the breakthrough came when I replaced the 'startdate / enddate' names with the text box names. Startdate and Enddate were the text box captions. I would have thought I would be able to use the caption names?

Anyhow, I might now go back and put in the cdate etc, just to make things a bit more secure, but pretty pleased that things are working. Have wanted to be able to query like this, for ages.

Really appreciate the help. Lots of other things I need to do with a database I'm using, so no doubt we'll talk again.

regards
stu

WHERE ((Employees.EmployeeName) Like "*" & Forms!frmQBFDraft2!Text0 & "*" Or Forms!frmQBFDraft2!Text0 Is Null) And ((Suppliers.SupplierName) Like "*" & Forms!frmQBFDraft2!Text2 & "*" Or Forms!frmQBFDraft2!Text2 Is Null) And ((([hours worked].date) Between Forms!frmQBFDraft2!text6 And Forms!frmQBFDraft2!text8) Or (Forms!frmQBFDraft2!text6 Is Null));
Hi stu,
Glad you got it resolved. Yes, as you discovered on your own, form controls are generally referred to by their name property. Re:CDate: As long as the string entered is recognized as a date in a numeric expression, there is no need for CDate.
Jun 17 '07 #12

Post your reply

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