pass values to query as criteria from form

[Trevor2007]

I have a query that setting date from and date to from form values works:
>=[forms]![E-Report query]![DateFrom] And <=[forms]![E-Report query]![DateTo]

but now I am trying to add another peramiter to pass in adition to above and now it doesn't work, I get a blank table when I run it and when i run the criteria just for State by

Forms![E-Report query]![State] I get a blank form as well,
what I'm trying to do is is set criteria of query with
>=[forms]![E-Report query]![DateFrom] And <=[forms]![E-Report query]![DateTo] And [forms]![E-Report query]![State]
and when i run it I get a blank table
note(the state field on the form is what the [state]value in the table equles

thanks for help in advance

[Trevor2007]

I have a query that setting date from and date to from form values works:
>=[forms]![E-Report query]![DateFrom] And <=[forms]![E-Report query]![DateTo]

but now I am trying to add another peramiter to pass in adition to above and now it doesn't work, I get a blank table when I run it and when i run the criteria just for State by

Forms![E-Report query]![State] I get a blank form as well,
what I'm trying to do is is set criteria of query with
>=[forms]![E-Report query]![DateFrom] And <=[forms]![E-Report query]![DateTo] And [forms]![E-Report query]![State]
and when i run it I get a blank table
note(the state field on the form is what the [state]value in the table equles

thanks for help in advance

OK I figured it out but the purpose of this will be to loop though to send email(s) from the query.
when I loop though to populate my recipients is there a way to specify to only use distinct email address until .EOF
Ie: Im using send object method to send my email and and SendTo for my recipient field
so when I sent up my loop my SendTo would be SendTo = sendTo & .fields("Email") , but like i said a few lines up how would I state in this loop that SendTo = Sendto &.fields ("Email") but only distinct email address? I'm guesing:
SendTo = SendTo & .Distinct fields
help please , thanx in advance

[Scott Price]

Excuse me, but your question lacks quite a bit in clarity! Please post for us the relevant code that you are asking about, and state in clear language what it is you are trying to do.

Regards,
Scott

[Trevor2007]

Excuse me, but your question lacks quite a bit in clarity! Please post for us the relevant code that you are asking about, and state in clear language what it is you are trying to do.

Regards,
Scott

ok I have code to open rst and select fields
to populate an email but how can I select distinct email addresses out of the rst (the rst is a query).
the problem I am having with my query rst now is when I run it I get an error "too fiew peramiters expected 3" (debuger highlights Set loRst = loDb.OpenRecordset("QryE-CallReport") )
but the same method works fine if the query is only comprised of one table , in this case it is two tables,
here is my query in Sql:

SELECT [VMSU-CLT].Date, [VMSU-CLT].Time, [VMSU-CLT].State, [VMSU-CLT].VISTALastName, [VMSU-CLT].VISTAFirstName, [VMSU-CLT].VISTAMiddleName, [VMSU-CLT].NSPID, [VMSU-CLT].ReasonForCall, StateEmpLU.RepEmail
FROM [VMSU-CLT], StateEmpLU
WHERE ((([VMSU-CLT].Date)>=[forms]![E-Report query]![DateFrom] And ([VMSU-CLT].Date)<=[forms]![E-Report query]![DateTo]) AND (([VMSU-CLT].State)=[forms]![E-Report query]![State]) AND ((StateEmpLU.State)=[forms]![E-Report query]![State]));

here is my code to populate an email:

Dim stcontents As String
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set loDb = CurrentDb
Set loRst = loDb.OpenRecordset("QryE-CallReport")
With loRst
Do Until .EOF
SendTo = SendTo & .Fields("VMSUTech") & ","
'stcontents = stcontents & .Fields("VMSUTech") & ","
.MoveNext
Loop
End With
 
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
 
 
stSubject = Me.subject
stBody = Me.msgBody & stcontents
DoCmd.SendObject , , acFormatTXT, SendTo, , also, stSubject, stBody, 1
 
 
 
Endd:
 
Exit_Print_Preview_Click:
    Exit Sub
 
Err_cmdReport_Click:
 
    MsgBox Err.Description
    Resume Exit_Print_Preview_Click
 
 
End Sub

Thanks for helping.

[Scott Price]

First of all, you need to join your two tables in your query expression. The query you have will not return much of anything meaningful... You need to tell the database which field each table has in common that will be used to relate the email address field in one table to the chosen employee/person in the other table.

Second you can use the DISTINCT or DISTINCTROW keyword in the query to return a distinct value.

You can look into the Access help file, go to the Table of Contents or Topics (depending on your version) and look under Microsoft Jet SQL Reference. In the Data Manipulation Section the article for SELECT Statement will give you a link to an article that explains the uses and syntax for the ALL/DISTINCT/DISTINCTROW keywords.

Regards,
Scott

[Trevor2007]

ok I have code to open rst and select fields
to populate an email but how can I select distinct email addresses out of the rst (the rst is a query).
the problem I am having with my query rst now is when I run it I get an error "too fiew peramiters expected 3" (debuger highlights Set loRst = loDb.OpenRecordset("QryE-CallReport") )
but the same method works fine if the query is only comprised of one table , in this case it is two tables,
here is my query in Sql:

SELECT [VMSU-CLT].Date, [VMSU-CLT].Time, [VMSU-CLT].State, [VMSU-CLT].VISTALastName, [VMSU-CLT].VISTAFirstName, [VMSU-CLT].VISTAMiddleName, [VMSU-CLT].NSPID, [VMSU-CLT].ReasonForCall, StateEmpLU.RepEmail
FROM [VMSU-CLT], StateEmpLU
WHERE ((([VMSU-CLT].Date)>=[forms]![E-Report query]![DateFrom] And ([VMSU-CLT].Date)<=[forms]![E-Report query]![DateTo]) AND (([VMSU-CLT].State)=[forms]![E-Report query]![State]) AND ((StateEmpLU.State)=[forms]![E-Report query]![State]));

here is my code to populate an email:

Dim stcontents As String
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set loDb = CurrentDb
Set loRst = loDb.OpenRecordset("QryE-CallReport")
With loRst
Do Until .EOF
SendTo = SendTo & .Fields("VMSUTech") & ","
'stcontents = stcontents & .Fields("VMSUTech") & ","
.MoveNext
Loop
End With
 
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
 
 
stSubject = Me.subject
stBody = Me.msgBody & stcontents
DoCmd.SendObject , , acFormatTXT, SendTo, , also, stSubject, stBody, 1
 
 
 
Endd:
 
Exit_Print_Preview_Click:
    Exit Sub
 
Err_cmdReport_Click:
 
    MsgBox Err.Description
    Resume Exit_Print_Preview_Click
 
 
End Sub

Thanks for helping.

I managed to solve by changing my code to reflect the tables instead of a query :

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
Dim loRst1 As DAO.Recordset
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" _
& " [State]= '" & Me.[State] & "';")
Set loRst1 = CurrentDb.OpenRecordset("SELECT * FROM [StateEmpLU] WHERE" _
& " [State]= '" & Me.[State] & "';")
With loRst
With loRst1
Do Until .EOF
SendTo = SendTo & loRst1.Fields("RepEmail") & ""
stBody = stBody & "Date: " & loRst.Fields("Date") & Chr$(9) & loRst.Fields("Time") & ","
.MoveNext
Loop
End With: End With
 
loRst.Close: loRst1.Close
Set loRst = Nothing: Set loRst1 = Nothing
Set loDb = Nothing
EditMessage = 1
stSubject = "test"
 
DoCmd.SendObject , , acFormatTXT, SendTo, , also, stSubject, stBody, 1

note: there are a few doble arguments but I am takeing care of those as I compile stbody
thanks to those who replied
I need to add to my where clause a date from and date to on the [VMSU-CLT] Table
can someone help

[Trevor2007]

I managed to solve by changing my code to reflect the tables instead of a query :

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
Dim loRst1 As DAO.Recordset
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" _
& " [State]= '" & Me.[State] & "';")
Set loRst1 = CurrentDb.OpenRecordset("SELECT * FROM [StateEmpLU] WHERE" _
& " [State]= '" & Me.[State] & "';")
With loRst
With loRst1
Do Until .EOF
SendTo = SendTo & loRst1.Fields("RepEmail") & ""
stBody = stBody & "Date: " & loRst.Fields("Date") & Chr$(9) & loRst.Fields("Time") & ","
.MoveNext
Loop
End With: End With

loRst.Close: loRst1.Close
Set loRst = Nothing: Set loRst1 = Nothing
Set loDb = Nothing
EditMessage = 1
stSubject = "test"

DoCmd.SendObject , , acFormatTXT, SendTo, , also, stSubject, stBody, 1

note: there are a few doble arguments but I am takeing care of those as I compile stbody
thanks to those who replied
I need to add to my where clause a date from and date to on the [VMSU-CLT] Table
can someone help

this is supose to select only those records where the state = Me.state and the record date is between Me.Datefrom and Me.DateTo
but when I added the date argument to the where clause it seems to bypass my state = Me.state argument and only produces the records between those dates

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" _
& " [State]= '" & Me.[State] & "' & [Date]>= '" & Me.[DateFrom] & "' & [Date]<= '" & Me.[DateTo] & "';")

what am I doing wrong here?

[Stewart Ross]

Hi. In the date part of your WHERE clause you've mistakenly used ampersands in place of ANDs - easy to do and not so easy to spot it once it's written.

set loRst ... WHERE" & " [State]= '" & Me.[State] & "' & [Date]>= '" & Me.[DateFrom] & "' & [Date]<= '" & Me.[DateTo] & "';")

It is unlikely to be selecting records between the correct dates at present and certainly can't filter the State values. Replacing the ampersands:

Set loRst ... WHERE" & " [State]= '" & Me.[State] & "' AND [Date]>= '" & Me.[DateFrom] & "' AND [Date]<= '" & Me.[DateTo] & "';")

-Stewart

[NeoPa]

this is supose to select only those records where the state = Me.state and the record date is between Me.Datefrom and Me.DateTo
but when I added the date argument to the where clause it seems to bypass my state = Me.state argument and only produces the records between those dates

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" _
& " [State]= '" & Me.[State] & "' & [Date]>= '" & Me.[DateFrom] & "' & [Date]<= '" & Me.[DateTo] & "';")
what am I doing wrong here?

Trevor,

Two of your posts IN THIS THREAD have already been edited for you to add the [ CODE ] tags that are required when posting code on this site (See How to Ask a Question). Please remember in future to put your code in the tags provide.

Admin.

[Trevor2007]

Trevor,

Two of your posts IN THIS THREAD have already been edited for you to add the [ CODE ] tags that are required when posting code on this site (See How to Ask a Question). Please remember in future to put your code in the tags provide.

Admin.

I am now getting an error 3464 error in critrea and highlights my query:

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" & " [State]= '" & Me.[State] & "' AND [Date]>= '" & Me.[DateFrom] & "' AND [Date]<= '" & Me.[DateTo] & "';")

[Stewart Ross]

I am now getting an error 3464 error in critrea and highlights my query:

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE" & " [State]= '" & Me.[State] & "' AND [Date]>= '" & Me.[DateFrom] & "' AND [Date]<= '" & Me.[DateTo] & "';")
 

Hi Trevor. I hadn't spotted earlier that in addition to your ampersand issue you were using quotes on either side of your dates, which treats the dates as if they were string literals. Assuming Date and DateTo are indeed of type date, these should be enclosed by hashes, not single quotes:

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-CLT] WHERE [State]= '" & Me.[State] & "' AND [Date]>= #" & Me.[DateFrom] & "# AND [Date]<= #" & Me.[DateTo] & "#;")
 

As an aside, although in a table definition it is legal to have a field called Date of type Date/Time, it is not good naming practice. Use of this field may be confused with the in-built Date function.

-Stewart

ps I am assuming that your State value is also a string, as it too is in single quotes. if it isn't remove the single quotes from either side.