Filtering a form using a combo and 2 dates

  • Thread starter Thread starter Daniel O'Neill
  • Start date Start date
D

Daniel O'Neill

Hi, i am trying to filter a form using a combo box containing the
address of an outlet, where the user can select the outlet and then
enter 2 dates in seperate text boxes, the idea is that once the user
hits the command button the filter will select the outlet and display
all the rentals attached to that outlet in between those dates in a
seperate form called Outlet. I am having trouble with the SQL! here is
what i have been trying.....
Private Sub cmdSearch_Click()
Dim strForm As String
Dim strSQL As String

strForm = "Outlet"

strSQL = "OutletCity = " & Me![lstOutlet] & " And ("dateStart = "
& Form_RentalAgreement.[dateStart]) Between #" & Me![txtStart] & "#
And #" & Me![txtEnd] & "#"

DoCmd.OpenForm strForm, , , strSQL
End Sub
I am trying to read in the date from another form called
RentalAgreement, I realise i could probably do this with a query but
under the guidelines of the project i must use VBA, i would really
appreciate it if anyone has any info on this particular issue as i
have tried to research it but to no avail!
Thank you
Daniel
 
Hi Daniel,

I'm not quite sure what you are doing with your SQL string. Let me spell
it out and maybe you might see where it's broken:

Original:
strSQL = "OutletCity = " & Me![lstOutlet] & " And ("dateStart = "
& Form_RentalAgreement.[dateStart]) Between #" & Me![txtStart] & "#
And #" & Me![txtEnd] & "#"

New:
strSQL = "OutletCity = " & Me![lstOutlet] & " And
dateStart Between #" & Me![txtStart] & "# And #" & Me![txtEnd] & "#"

What are you trying to do with:
("dateStart = " & Form_RentalAgreement.[dateStart]) Between ....


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| From: (e-mail address removed) (Daniel O'Neill)
| Newsgroups: microsoft.public.access.forms
| Subject: Filtering a form using a combo and 2 dates
| Date: 1 Mar 2004 09:48:51 -0800
| Organization: http://groups.google.com
| Lines: 26
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 143.239.89.201
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1078163331 1633 127.0.0.1 (1 Mar 2004
17:48:51 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Mon, 1 Mar 2004 17:48:51 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!border2.nntp.ash.giganews.com!border1.nntp.ash.giganews.com!nntp.gigane
ws.com!news.glorb.com!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:257469
| X-Tomcat-NG: microsoft.public.access.forms
|
| Hi, i am trying to filter a form using a combo box containing the
| address of an outlet, where the user can select the outlet and then
| enter 2 dates in seperate text boxes, the idea is that once the user
| hits the command button the filter will select the outlet and display
| all the rentals attached to that outlet in between those dates in a
| seperate form called Outlet. I am having trouble with the SQL! here is
| what i have been trying.....
| Private Sub cmdSearch_Click()
| Dim strForm As String
| Dim strSQL As String
|
| strForm = "Outlet"
|
| strSQL = "OutletCity = " & Me![lstOutlet] & " And ("dateStart = "
| & Form_RentalAgreement.[dateStart]) Between #" & Me![txtStart] & "#
| And #" & Me![txtEnd] & "#"
|
| DoCmd.OpenForm strForm, , , strSQL
| End Sub
| I am trying to read in the date from another form called
| RentalAgreement, I realise i could probably do this with a query but
| under the guidelines of the project i must use VBA, i would really
| appreciate it if anyone has any info on this particular issue as i
| have tried to research it but to no avail!
| Thank you
| Daniel
|
 
Basically i am trying to compare a field called dateStart in another
table called RentalAgreement to the values that the user enters into
the two txt boxes on my filter form, i have modified the string to
look like this.....

strSQL = "[Outlet Number]= " & Me.lstOutlet & " And [dateStart]
Between #" & Me.txtStart & "# and #" & Me.txtEnd & "#"

The problem here seems to be the "dateStart" field, it doesnt seem to
be able to link it to the RentalAgreement table, and instead asks for
a parameter to be entered by the user,
hope that explains it a little better,
Thank you very much
Regards
Daniel
 
Hi Daniel,

It's not clear what you are attempting to do!!

When you do something like
DoCmd.OpenForm strForm, , , strSQL
you are saying open the Form with Records that match this string

Thereby, open my Form that is based on "XYZ table/query" but only return
the records that match this string.

What you are probably running into is:
- The Form is based on some other table than "RentalAgreement" that does
not have a field called "dateStart"
- Thereby you are prompted for parameter value.... it can't find the
field in the table/query

So it's like saying:
Return ALL the records from the table "XYZ" where the field in the table
"RentalAgreement" is between some values. Microsoft Access returns a
parameter value (question) how am I to match records in "XYZ" where the
value in "RentalAgreement" is between some value??

What you maybe looking to do is:
Create a SQL string that INNER JOIN "XYZ" with "RentalAgreement" along with
a Between Clause, example:

SELECT XYZ.*
FROM XYZ INNER JOIN RentalAgreement ON XYZ.RecordID =
RentalAgreement.RecordID
WHERE (((RentalAgreement.MyField) Between #1/1/2003# And #1/15/2003#));

Eric

--------------------
| From: (e-mail address removed) (Daniel O'Neill)
| Newsgroups: microsoft.public.access.forms
| Subject: Re: Filtering a form using a combo and 2 dates
| Date: 2 Mar 2004 03:26:22 -0800
| Organization: http://groups.google.com
| Lines: 15
| Message-ID: <[email protected]>
| References: <[email protected]>
<jpfxjr#[email protected]>
| NNTP-Posting-Host: 143.239.89.201
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1078226783 6878 127.0.0.1 (2 Mar 2004
11:26:23 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Tue, 2 Mar 2004 11:26:23 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!
news.maxwell.syr.edu!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:257585
| X-Tomcat-NG: microsoft.public.access.forms
|
| Basically i am trying to compare a field called dateStart in another
| table called RentalAgreement to the values that the user enters into
| the two txt boxes on my filter form, i have modified the string to
| look like this.....
|
| strSQL = "[Outlet Number]= " & Me.lstOutlet & " And [dateStart]
| Between #" & Me.txtStart & "# and #" & Me.txtEnd & "#"
|
| The problem here seems to be the "dateStart" field, it doesnt seem to
| be able to link it to the RentalAgreement table, and instead asks for
| a parameter to be entered by the user,
| hope that explains it a little better,
| Thank you very much
| Regards
| Daniel
|
 
Thanks Eric,
I actually solved the problem in a round about way, i just used the
DoCmd.OpenReport function and displayed my data via a report, and it
worked fine, so there must have been some glitch with the form i was
using!
Thanks again
regards
Daniel


(e-mail address removed) ("prabha") wrote in message
Hi Daniel,

It's not clear what you are attempting to do!!

When you do something like
DoCmd.OpenForm strForm, , , strSQL
you are saying open the Form with Records that match this string

Thereby, open my Form that is based on "XYZ table/query" but only return
the records that match this string.

What you are probably running into is:
- The Form is based on some other table than "RentalAgreement" that does
not have a field called "dateStart"
- Thereby you are prompted for parameter value.... it can't find the
field in the table/query

So it's like saying:
Return ALL the records from the table "XYZ" where the field in the table
"RentalAgreement" is between some values. Microsoft Access returns a
parameter value (question) how am I to match records in "XYZ" where the
value in "RentalAgreement" is between some value??

What you maybe looking to do is:
Create a SQL string that INNER JOIN "XYZ" with "RentalAgreement" along with
a Between Clause, example:

SELECT XYZ.*
FROM XYZ INNER JOIN RentalAgreement ON XYZ.RecordID =
RentalAgreement.RecordID
WHERE (((RentalAgreement.MyField) Between #1/1/2003# And #1/15/2003#));

Eric

--------------------
| From: (e-mail address removed) (Daniel O'Neill)
| Newsgroups: microsoft.public.access.forms
| Subject: Re: Filtering a form using a combo and 2 dates
| Date: 2 Mar 2004 03:26:22 -0800
| Organization: http://groups.google.com
| Lines: 15
| Message-ID: <[email protected]>
| References: <[email protected]>
<jpfxjr#[email protected]>
| NNTP-Posting-Host: 143.239.89.201
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1078226783 6878 127.0.0.1 (2 Mar 2004
11:26:23 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Tue, 2 Mar 2004 11:26:23 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!
news.maxwell.syr.edu!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:257585
| X-Tomcat-NG: microsoft.public.access.forms
|
| Basically i am trying to compare a field called dateStart in another
| table called RentalAgreement to the values that the user enters into
| the two txt boxes on my filter form, i have modified the string to
| look like this.....
|
| strSQL = "[Outlet Number]= " & Me.lstOutlet & " And [dateStart]
| Between #" & Me.txtStart & "# and #" & Me.txtEnd & "#"
|
| The problem here seems to be the "dateStart" field, it doesnt seem to
| be able to link it to the RentalAgreement table, and instead asks for
| a parameter to be entered by the user,
| hope that explains it a little better,
| Thank you very much
| Regards
| Daniel
|
Click to expand...
 
Back
Top