 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / July 2008Have date from form as query criteria
Hi Was just wondering how I would use a Date variable declared in my vba code on a form as the criteria in query. What is the synax is what i mean. I can only seem to find it for text box values this is 2 variable StartDate and EndDate that get their vaues from 6 combo boxes (3 & 3). Thanks in advance. The query cannot use VBA variables directly. You will need to place a (hidden?) text box on the form, and assign the value of the variable to the text box. If you are using Access 2007, you may be able to use TempVars. The query can read them in this version.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi > Was just wondering how I would use a Date variable declared in my vba code [quoted text clipped - 5 lines] > (3 & 3). > Thanks in advance. Another option is to create a public function that returns the value of the variable, and use the function in the query. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > The query cannot use VBA variables directly. > [quoted text clipped - 14 lines] >> (3 & 3). >> Thanks in advance. In addition to what Allen said, it's been my experience that you need to use the Eval function in the query criteria, so for example a text box called txtDate on form frmMain your criteria would be Eval("Forms!frmMain!txtDate"). You need the quotes around the control name. > Hi > Was just wondering how I would use a Date variable declared in my vba code [quoted text clipped - 4 lines] > (3 & 3). > Thanks in advance. I have never had to use the Eval function to refer to the value of a text box on a form in a query criteria. Under what circumstances did you find that necessary? SignatureDave Hargis, Microsoft Access MVP > In addition to what Allen said, it's been my experience that you need to use > the Eval function in the query criteria, so for example a text box called [quoted text clipped - 9 lines] > > (3 & 3). > > Thanks in advance. Offhand I don't remember, but I know there are times where it is necessary. I thought dates was one of them, but maybe I'm wrong about that. It may be with combobox values only?? I've had enough problems with it where I just got into the habit of always using Eval. > I have never had to use the Eval function to refer to the value of a text box > on a form in a query criteria. [quoted text clipped - 13 lines] > > > (3 & 3). > > > Thanks in advance. Interesting. with dates, they need to be delimited with # The only other thing I can think of would be trying to use a column other than the bound column of a combo. A query can't understand that, but I have never tried that so I don't know if an Eval would resolve it or not. Just curious. SignatureDave Hargis, Microsoft Access MVP > Offhand I don't remember, but I know there are times where it is necessary. I > thought dates was one of them, but maybe I'm wrong about that. It may be with [quoted text clipped - 18 lines] > > > > (3 & 3). > > > > Thanks in advance. When I use textbox dates in criteria in a 'stored' query, I use Eval, and there is no need for #. If I specify dates in an SQL string in VBA code, then I include the # around the date value, e.g. "WHERE tbl1.myDate = #" & txtDate & "#" As for the combobox thing, you may be right. I know I sometimes use a column from a combobox that is not the bound column in criteria, so maybe that's the only instance where you need Eval with a combo box. But query cirtieria with form controls has caused me enough problems to the point where I always use Eval, and it works every time. Maybe not quite as efficient, but I know it works! > Interesting. with dates, they need to be delimited with # > The only other thing I can think of would be trying to use a column other [quoted text clipped - 25 lines] > > > > > (3 & 3). > > > > > Thanks in advance. Not doubting it will work, just never had a case where I need it. SignatureDave Hargis, Microsoft Access MVP > When I use textbox dates in criteria in a 'stored' query, I use Eval, and > there is no need for #. If I specify dates in an SQL string in VBA code, then [quoted text clipped - 38 lines] > > > > > > (3 & 3). > > > > > > Thanks in advance. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.