SQL help

[troy_lee@comcast.net]

I have two fields on a form. These two fields' values are based on an
expression and represent a date range. I need to create a SQL
statement that will use the returned values of these two fields in the
WHERE clause as a date range (less than or equal to, and greater than
or equal to the upper and lower dates of the range).

I then need to count the total number of records returned from the
query and output the record count to another text box on the same form
as the date range fields.

This seems like it should be easy but I am having problems and I can't
determine if it's my syntax for the query or if it's impossible to
call values from a text box directly for use in the WHERE clause.

Here is what I have played around with. I don't get an error when the
form runs. I just don't get a returned record count in my text box.

strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " & _
"Where RMAs.[QAreviewed Date] Between Me!
[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
DoCmd.RunSQL strTotalShips
Me!txtWk1TotalS hips = strTotalShips

Thanks for the help in advance.

Troy Lee

[Salad]

Re: SQL help

troy_lee@comcas t.net wrote:

I have two fields on a form. These two fields' values are based on an
expression and represent a date range. I need to create a SQL
statement that will use the returned values of these two fields in the
WHERE clause as a date range (less than or equal to, and greater than
or equal to the upper and lower dates of the range).
>
I then need to count the total number of records returned from the
query and output the record count to another text box on the same form
as the date range fields.
>
This seems like it should be easy but I am having problems and I can't
determine if it's my syntax for the query or if it's impossible to
call values from a text box directly for use in the WHERE clause.
>
Here is what I have played around with. I don't get an error when the
form runs. I just don't get a returned record count in my text box.
>
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " & _
"Where RMAs.[QAreviewed Date] Between Me!
[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
DoCmd.RunSQL strTotalShips
Me!txtWk1TotalS hips = strTotalShips
>
Thanks for the help in advance.
>
Troy Lee

RunSQL, as well as Execute, are used for action queries (update/delete,
etc).

You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing

Bo

http://www.youtube.com/watch?v=6vwXAgexoto

[troy_lee@comcast.net]

Re: SQL help

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

I have two fields on a form. These two fields' values are based on an
expression and represent a date range. I need to create a SQL
statement that will use the returned values of these two fields in the
WHERE clause as a date range (less than or equal to, and greater than
or equal to the upper and lower dates of the range).

>

I then need to count the total number of records returned from the
query and output the record count to another text box on the same form
as the date range fields.

>

This seems like it should be easy but I am having problems and I can't
determine if it's my syntax for the query or if it's impossible to
call values from a text box directly for use in the WHERE clause.

>

Here is what I have played around with. I don't get an error when the
form runs. I just don't get a returned record count in my text box.

>

strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " & _
"Where RMAs.[QAreviewed Date] Between Me!
[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
DoCmd.RunSQL strTotalShips
Me!txtWk1TotalS hips = strTotalShips

>

Thanks for the help in advance.

>

Troy Lee

>
RunSQL, as well as Execute, are used for action queries (update/delete,
etc).
>
You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing
>
Bohttp://www.youtube.com/watch?v=6vwXAge xoto

Thank you so much. Seems like a perfect solution. I will post on how
it turns out.

Troy

[troy_lee@comcast.net]

Re: SQL help

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

I have two fields on a form. These two fields' values are based on an
expression and represent a date range. I need to create a SQL
statement that will use the returned values of these two fields in the
WHERE clause as a date range (less than or equal to, and greater than
or equal to the upper and lower dates of the range).

>

I then need to count the total number of records returned from the
query and output the record count to another text box on the same form
as the date range fields.

>

This seems like it should be easy but I am having problems and I can't
determine if it's my syntax for the query or if it's impossible to
call values from a text box directly for use in the WHERE clause.

>

Here is what I have played around with. I don't get an error when the
form runs. I just don't get a returned record count in my text box.

>

strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " & _
"Where RMAs.[QAreviewed Date] Between Me!
[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
DoCmd.RunSQL strTotalShips
Me!txtWk1TotalS hips = strTotalShips

>

Thanks for the help in advance.

>

Troy Lee

>
RunSQL, as well as Execute, are used for action queries (update/delete,
etc).
>
You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing
>
Bohttp://www.youtube.com/watch?v=6vwXAge xoto

Salad,
Thanks for the help. It works perfect. I have another question.

I want to use the same parameters for the query except that I need to
examine the recordset and count two different things in it.

One, I need to count all the records where one of the fields = "2017"
and return that count to a text box on the form. I then need to count
all the other records that are not 2017 and output that to another
text box. Can you help with this portion of the SQL statement?

Thanks.

Troy

[Salad]

Re: SQL help

troy_lee@comcas t.net wrote:

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:
>

>>troy_...@comc ast.net wrote:
>>

>>>I have two fields on a form. These two fields' values are based on an
>>>expression and represent a date range. I need to create a SQL
>>>statement that will use the returned values of these two fields in the
>>>WHERE clause as a date range (less than or equal to, and greater than
>>>or equal to the upper and lower dates of the range).

>>

>>>I then need to count the total number of records returned from the
>>>query and output the record count to another text box on the same form
>>>as the date range fields.

>>

>>>This seems like it should be easy but I am having problems and I can't
>>>determine if it's my syntax for the query or if it's impossible to
>>>call values from a text box directly for use in the WHERE clause.

>>

>>>Here is what I have played around with. I don't get an error when the
>>>form runs. I just don't get a returned record count in my text box.

>>

>>>strTotalShip s = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>> "From RMAs " & _
>> "Where RMAs.[QAreviewed Date] Between Me!
>>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
>> DoCmd.RunSQL strTotalShips
>> Me!txtWk1TotalS hips = strTotalShips

>>

>>>Thanks for the help in advance.

>>

>>>Troy Lee

>>
>>RunSQL, as well as Execute, are used for action queries (update/delete,
>>etc).
>>
>>You could set it to a recordset
> dim r as DAO.recordset
> Dim strTotalships as string
> 'surround dates in #
> strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
> "From RMAs " Where RMAs.[QAreviewed Date] " & _
> "Between #" & Me![txtWk1BegDate] " & "# AND #" & _
> Me![txtWk1EndDate] & "#;"
> set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
> 'move to last record to get true recordcount
> If r.RecordCount 0 then r.movelast
> Me!txtWk1TotalS hips = r.RecordCount
> r.close
> set r = Nothing
>>
>>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>
>
Salad,
Thanks for the help. It works perfect. I have another question.
>
I want to use the same parameters for the query except that I need to
examine the recordset and count two different things in it.
>
One, I need to count all the records where one of the fields = "2017"
and return that count to a text box on the form. I then need to count
all the other records that are not 2017 and output that to another
text box. Can you help with this portion of the SQL statement?
>
Thanks.
>
Troy

There's multiple ways of doing it. I might use a totals query. Don't
know if FldName is a numeric or character value...if character then
surround by a ', else no '. Ex:

strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
'force a zero if count is blank with NZ
me.Is2017 = NZ(r!Cnt2017,0)
me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
r.close
set r = nothing

Three Birds

http://www.youtube.com/watch?v=rnDrbagYm24

[troy_lee@comcast.net]

Re: SQL help

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>

>troy_...@comca st.net wrote:

>

>>I have two fields on a form. These two fields' values are based on an
>>expression and represent a date range. I need to create a SQL
>>statement that will use the returned values of these two fields in the
>>WHERE clause as a date range (less than or equal to, and greater than
>>or equal to the upper and lower dates of the range).

>

>>I then need to count the total number of records returned from the
>>query and output the record count to another text box on the same form
>>as the date range fields.

>

>>This seems like it should be easy but I am having problems and I can't
>>determine if it's my syntax for the query or if it's impossible to
>>call values from a text box directly for use in the WHERE clause.

>

>>Here is what I have played around with. I don't get an error when the
>>form runs. I just don't get a returned record count in my text box.

>

>>strTotalShi ps = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
> "From RMAs " & _
> "Where RMAs.[QAreviewed Date] Between Me!
>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
> DoCmd.RunSQL strTotalShips
> Me!txtWk1TotalS hips = strTotalShips

>

>>Thanks for the help in advance.

>

>>Troy Lee

>

>RunSQL, as well as Execute, are used for action queries (update/delete,
>etc).

>

>You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing

>

>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>

Salad,
Thanks for the help. It works perfect. I have another question.

>

I want to use the same parameters for the query except that I need to
examine the recordset and count two different things in it.

>

One, I need to count all the records where one of the fields = "2017"
and return that count to a text box on the form. I then need to count
all the other records that are not 2017 and output that to another
text box. Can you help with this portion of the SQL statement?

>

Thanks.

>

Troy

>
There's multiple ways of doing it. I might use a totals query. Don't
know if FldName is a numeric or character value...if character then
surround by a ', else no '. Ex:
>
strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
'force a zero if count is blank with NZ
me.Is2017 = NZ(r!Cnt2017,0)
me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
r.close
set r = nothing
>
Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

Salad,
Thanks again. Looks great. Now for the other units that are not 2017,
can I preface parts of the SELECT

[troy_lee@comcast.net]

Re: SQL help

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>

>troy_...@comca st.net wrote:

>

>>I have two fields on a form. These two fields' values are based on an
>>expression and represent a date range. I need to create a SQL
>>statement that will use the returned values of these two fields in the
>>WHERE clause as a date range (less than or equal to, and greater than
>>or equal to the upper and lower dates of the range).

>

>>I then need to count the total number of records returned from the
>>query and output the record count to another text box on the same form
>>as the date range fields.

>

>>This seems like it should be easy but I am having problems and I can't
>>determine if it's my syntax for the query or if it's impossible to
>>call values from a text box directly for use in the WHERE clause.

>

>>Here is what I have played around with. I don't get an error when the
>>form runs. I just don't get a returned record count in my text box.

>

>>strTotalShi ps = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
> "From RMAs " & _
> "Where RMAs.[QAreviewed Date] Between Me!
>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
> DoCmd.RunSQL strTotalShips
> Me!txtWk1TotalS hips = strTotalShips

>

>>Thanks for the help in advance.

>

>>Troy Lee

>

>RunSQL, as well as Execute, are used for action queries (update/delete,
>etc).

>

>You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing

>

>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>

Salad,
Thanks for the help. It works perfect. I have another question.

>

I want to use the same parameters for the query except that I need to
examine the recordset and count two different things in it.

>

One, I need to count all the records where one of the fields = "2017"
and return that count to a text box on the form. I then need to count
all the other records that are not 2017 and output that to another
text box. Can you help with this portion of the SQL statement?

>

Thanks.

>

Troy

>
There's multiple ways of doing it. I might use a totals query. Don't
know if FldName is a numeric or character value...if character then
surround by a ', else no '. Ex:
>
strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
'force a zero if count is blank with NZ
me.Is2017 = NZ(r!Cnt2017,0)
me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
r.close
set r = nothing
>
Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

Salad,
Thanks for the help again. Looks great. Can't wait to try it.

For those units that are not 2017s, what might I do to exclude 2017s
from the recordset?

Troy

[troy_lee@comcast.net]

Re: SQL help

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>

>troy_...@comca st.net wrote:

>

>>I have two fields on a form. These two fields' values are based on an
>>expression and represent a date range. I need to create a SQL
>>statement that will use the returned values of these two fields in the
>>WHERE clause as a date range (less than or equal to, and greater than
>>or equal to the upper and lower dates of the range).

>

>>I then need to count the total number of records returned from the
>>query and output the record count to another text box on the same form
>>as the date range fields.

>

>>This seems like it should be easy but I am having problems and I can't
>>determine if it's my syntax for the query or if it's impossible to
>>call values from a text box directly for use in the WHERE clause.

>

>>Here is what I have played around with. I don't get an error when the
>>form runs. I just don't get a returned record count in my text box.

>

>>strTotalShi ps = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
> "From RMAs " & _
> "Where RMAs.[QAreviewed Date] Between Me!
>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
> DoCmd.RunSQL strTotalShips
> Me!txtWk1TotalS hips = strTotalShips

>

>>Thanks for the help in advance.

>

>>Troy Lee

>

>RunSQL, as well as Execute, are used for action queries (update/delete,
>etc).

>

>You could set it to a recordset
dim r as DAO.recordset
Dim strTotalships as string
'surround dates in #
strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
"From RMAs " Where RMAs.[QAreviewed Date] " & _
"Between #" & Me![txtWk1BegDate] " & "# AND #" & _
Me![txtWk1EndDate] & "#;"
set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
'move to last record to get true recordcount
If r.RecordCount 0 then r.movelast
Me!txtWk1TotalS hips = r.RecordCount
r.close
set r = Nothing

>

>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>

Salad,
Thanks for the help. It works perfect. I have another question.

>

I want to use the same parameters for the query except that I need to
examine the recordset and count two different things in it.

>

One, I need to count all the records where one of the fields = "2017"
and return that count to a text box on the form. I then need to count
all the other records that are not 2017 and output that to another
text box. Can you help with this portion of the SQL statement?

>

Thanks.

>

Troy

>
There's multiple ways of doing it. I might use a totals query. Don't
know if FldName is a numeric or character value...if character then
surround by a ', else no '. Ex:
>
strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
'force a zero if count is blank with NZ
me.Is2017 = NZ(r!Cnt2017,0)
me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
r.close
set r = nothing
>
Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

Sorry about my last reply. I saw you included provisions for my
question in your last code submission.

One more question... How do I return the values me.Is2017 and
me.Not2017 to my form's text boxes?

Troy

[Salad]

Re: SQL help

troy_lee@comcas t.net wrote:

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:
>

>>troy_...@comc ast.net wrote:
>>

>>>On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>>

>>>>troy_...@co mcast.net wrote:

>>

>>>>>I have two fields on a form. These two fields' values are based on an
>>>>>expressi on and represent a date range. I need to create a SQL
>>>>>statemen t that will use the returned values of these two fields in the
>>>>>WHERE clause as a date range (less than or equal to, and greater than
>>>>>or equal to the upper and lower dates of the range).

>>

>>>>>I then need to count the total number of records returned from the
>>>>>query and output the record count to another text box on the same form
>>>>>as the date range fields.

>>

>>>>>This seems like it should be easy but I am having problems and I can't
>>>>>determin e if it's my syntax for the query or if it's impossible to
>>>>>call values from a text box directly for use in the WHERE clause.

>>

>>>>>Here is what I have played around with. I don't get an error when the
>>>>>form runs. I just don't get a returned record count in my text box.

>>

>>>>>strTotalSh ips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>>> "From RMAs " & _
>>>> "Where RMAs.[QAreviewed Date] Between Me!
>>>>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
>>>> DoCmd.RunSQL strTotalShips
>>>> Me!txtWk1TotalS hips = strTotalShips

>>

>>>>>Thanks for the help in advance.

>>

>>>>>Troy Lee

>>

>>>>RunSQL, as well as Execute, are used for action queries (update/delete,
>>>>etc).

>>

>>>>You could set it to a recordset
>>> dim r as DAO.recordset
>>> Dim strTotalships as string
>>> 'surround dates in #
>>> strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>> "From RMAs " Where RMAs.[QAreviewed Date] " & _
>>> "Between #" & Me![txtWk1BegDate] " & "# AND #" & _
>>> Me![txtWk1EndDate] & "#;"
>>> set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
>>> 'move to last record to get true recordcount
>>> If r.RecordCount 0 then r.movelast
>>> Me!txtWk1TotalS hips = r.RecordCount
>>> r.close
>>> set r = Nothing

>>

>>>>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>>

>>>Salad,
>>>Thanks for the help. It works perfect. I have another question.

>>

>>>I want to use the same parameters for the query except that I need to
>>>examine the recordset and count two different things in it.

>>

>>>One, I need to count all the records where one of the fields = "2017"
>>>and return that count to a text box on the form. I then need to count
>>>all the other records that are not 2017 and output that to another
>>>text box. Can you help with this portion of the SQL statement?

>>

>>>Thanks.

>>

>>>Troy

>>
>>There's multiple ways of doing it. I might use a totals query. Don't
>>know if FldName is a numeric or character value...if character then
>>surround by a ', else no '. Ex:
>>
> strSQL = "SELECT Count([FldNm]) AS CntAll," & _
> "Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
> "Where ..."
>>set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
>>'force a zero if count is blank with NZ
>>me.Is2017 = NZ(r!Cnt2017,0)
>>me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
>>r.close
>>set r = nothing
>>
>>Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

>
>
Sorry about my last reply. I saw you included provisions for my
question in your last code submission.
>

I could have used
strSQL = "SELECT Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017," & _
"Sum(IIf([FldNm]<>'2017',1,0) ) AS CntNot2017 From RMAs " & _
"Where ..."

One more question... How do I return the values me.Is2017 and
me.Not2017 to my form's text boxes?

Change my Me.Is2017 and Me.Not2017 to their respective text box names in
your form? You didn't supply their names.

Iran
http://ca.youtube.com/watch?v=uUjIA3Rt7gk

>
Troy

[troy_lee@comcast.net]

Re: SQL help

On Jun 6, 2:01 pm, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

>

>troy_...@comca st.net wrote:

>

>>On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>

>>>troy_...@com cast.net wrote:

>

>>>>I have two fields on a form. These two fields' values are based on an
>>>>expressio n and represent a date range. I need to create a SQL
>>>>statement that will use the returned values of these two fields in the
>>>>WHERE clause as a date range (less than or equal to, and greater than
>>>>or equal to the upper and lower dates of the range).

>

>>>>I then need to count the total number of records returned from the
>>>>query and output the record count to another text box on the same form
>>>>as the date range fields.

>

>>>>This seems like it should be easy but I am having problems and I can't
>>>>determine if it's my syntax for the query or if it's impossible to
>>>>call values from a text box directly for use in the WHERE clause.

>

>>>>Here is what I have played around with. I don't get an error when the
>>>>form runs. I just don't get a returned record count in my text box.

>

>>>>strTotalShi ps = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>> "From RMAs " & _
>>> "Where RMAs.[QAreviewed Date] Between Me!
>>>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
>>> DoCmd.RunSQL strTotalShips
>>> Me!txtWk1TotalS hips = strTotalShips

>

>>>>Thanks for the help in advance.

>

>>>>Troy Lee

>

>>>RunSQL, as well as Execute, are used for action queries (update/delete,
>>>etc).

>

>>>You could set it to a recordset
>> dim r as DAO.recordset
>> Dim strTotalships as string
>> 'surround dates in #
>> strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>> "From RMAs " Where RMAs.[QAreviewed Date] " & _
>> "Between #" & Me![txtWk1BegDate] " & "# AND #" & _
>> Me![txtWk1EndDate] & "#;"
>> set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
>> 'move to last record to get true recordcount
>> If r.RecordCount 0 then r.movelast
>> Me!txtWk1TotalS hips = r.RecordCount
>> r.close
>> set r = Nothing

>

>>>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>

>>Salad,
>>Thanks for the help. It works perfect. I have another question.

>

>>I want to use the same parameters for the query except that I need to
>>examine the recordset and count two different things in it.

>

>>One, I need to count all the records where one of the fields = "2017"
>>and return that count to a text box on the form. I then need to count
>>all the other records that are not 2017 and output that to another
>>text box. Can you help with this portion of the SQL statement?

>

>>Thanks.

>

>>Troy

>

>There's multiple ways of doing it. I might use a totals query. Don't
>know if FldName is a numeric or character value...if character then
>surround by a ', else no '. Ex:

>

strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
>set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
>'force a zero if count is blank with NZ
>me.Is2017 = NZ(r!Cnt2017,0)
>me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
>r.close
>set r = nothing

>

>Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

>

Sorry about my last reply. I saw you included provisions for my
question in your last code submission.

>
I could have used
strSQL = "SELECT Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017," & _
"Sum(IIf([FldNm]<>'2017',1,0) ) AS CntNot2017 From RMAs " & _
"Where ..."
>

One more question... How do I return the values me.Is2017 and
me.Not2017 to my form's text boxes?

>
Change my Me.Is2017 and Me.Not2017 to their respective text box names in
your form? You didn't supply their names.
>
Iranhttp://ca.youtube.com/watch?v=uUjIA3R t7gk
>
>
>

Troy

I am getting a failure on the Select statement. It says that I am
missing an operator. This is what I have:

"SELECT Count(RMAs.[Part No 2540] AS CntAll, Sum(IIF RMAs.[Part No
2540] = '2017',1,0)) AS Cnt2017 "

I noticed that on the first query you supplied you included this right
after the word Select:

"SELECT RMAs.*, RMAs.[QAreviewed Date]

Do I need a reference like this before I start the count action?

I really can't think what operator I might be missing...

Troy

[troy_lee@comcast.net]

Re: SQL help

On Jun 6, 2:01 pm, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.net wrote:

On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

>

>troy_...@comca st.net wrote:

>

>>On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>

>>>troy_...@com cast.net wrote:

>

>>>>I have two fields on a form. These two fields' values are based on an
>>>>expressio n and represent a date range. I need to create a SQL
>>>>statement that will use the returned values of these two fields in the
>>>>WHERE clause as a date range (less than or equal to, and greater than
>>>>or equal to the upper and lower dates of the range).

>

>>>>I then need to count the total number of records returned from the
>>>>query and output the record count to another text box on the same form
>>>>as the date range fields.

>

>>>>This seems like it should be easy but I am having problems and I can't
>>>>determine if it's my syntax for the query or if it's impossible to
>>>>call values from a text box directly for use in the WHERE clause.

>

>>>>Here is what I have played around with. I don't get an error when the
>>>>form runs. I just don't get a returned record count in my text box.

>

>>>>strTotalShi ps = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>> "From RMAs " & _
>>> "Where RMAs.[QAreviewed Date] Between Me!
>>>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
>>> DoCmd.RunSQL strTotalShips
>>> Me!txtWk1TotalS hips = strTotalShips

>

>>>>Thanks for the help in advance.

>

>>>>Troy Lee

>

>>>RunSQL, as well as Execute, are used for action queries (update/delete,
>>>etc).

>

>>>You could set it to a recordset
>> dim r as DAO.recordset
>> Dim strTotalships as string
>> 'surround dates in #
>> strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>> "From RMAs " Where RMAs.[QAreviewed Date] " & _
>> "Between #" & Me![txtWk1BegDate] " & "# AND #" & _
>> Me![txtWk1EndDate] & "#;"
>> set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
>> 'move to last record to get true recordcount
>> If r.RecordCount 0 then r.movelast
>> Me!txtWk1TotalS hips = r.RecordCount
>> r.close
>> set r = Nothing

>

>>>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>

>>Salad,
>>Thanks for the help. It works perfect. I have another question.

>

>>I want to use the same parameters for the query except that I need to
>>examine the recordset and count two different things in it.

>

>>One, I need to count all the records where one of the fields = "2017"
>>and return that count to a text box on the form. I then need to count
>>all the other records that are not 2017 and output that to another
>>text box. Can you help with this portion of the SQL statement?

>

>>Thanks.

>

>>Troy

>

>There's multiple ways of doing it. I might use a totals query. Don't
>know if FldName is a numeric or character value...if character then
>surround by a ', else no '. Ex:

>

strSQL = "SELECT Count([FldNm]) AS CntAll," & _
"Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
"Where ..."
>set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
>'force a zero if count is blank with NZ
>me.Is2017 = NZ(r!Cnt2017,0)
>me.Not2017 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
>r.close
>set r = nothing

>

>Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

>

Sorry about my last reply. I saw you included provisions for my
question in your last code submission.

>
I could have used
strSQL = "SELECT Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017," & _
"Sum(IIf([FldNm]<>'2017',1,0) ) AS CntNot2017 From RMAs " & _
"Where ..."
>

One more question... How do I return the values me.Is2017 and
me.Not2017 to my form's text boxes?

>
Change my Me.Is2017 and Me.Not2017 to their respective text box names in
your form? You didn't supply their names.
>
Iranhttp://ca.youtube.com/watch?v=uUjIA3R t7gk
>
>
>

Troy

Never mind Salad. I got your second piece of code to work. I like it
much better anyways. Thanks you so much.

Troy

[Salad]

Re: SQL help

troy_lee@comcas t.net wrote:

On Jun 6, 2:01 pm, Salad <o...@vinegar.c omwrote:
>

>>troy_...@comc ast.net wrote:
>>

>>>On Jun 6, 10:06 am, Salad <o...@vinegar.c omwrote:

>>

>>>>troy_...@co mcast.net wrote:

>>

>>>>>On Jun 5, 2:50 pm, Salad <o...@vinegar.c omwrote:

>>

>>>>>>troy_...@ comcast.net wrote:

>>

>>>>>>>I have two fields on a form. These two fields' values are based on an
>>>>>>>expressi on and represent a date range. I need to create a SQL
>>>>>>>statemen t that will use the returned values of these two fields in the
>>>>>>>WHERE clause as a date range (less than or equal to, and greater than
>>>>>>>or equal to the upper and lower dates of the range).

>>

>>>>>>>I then need to count the total number of records returned from the
>>>>>>>query and output the record count to another text box on the same form
>>>>>>>as the date range fields.

>>

>>>>>>>This seems like it should be easy but I am having problems and I can't
>>>>>>>determin e if it's my syntax for the query or if it's impossible to
>>>>>>>call values from a text box directly for use in the WHERE clause.

>>

>>>>>>>Here is what I have played around with. I don't get an error when the
>>>>>>>form runs. I just don't get a returned record count in my text box.

>>

>>>>>>>strTotal Ships = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>>>>> "From RMAs " & _
>>>>>> "Where RMAs.[QAreviewed Date] Between Me!
>>>>>>>[txtWk1BegDate] AND Me![txtWk1EndDate]'; "
>>>>>> DoCmd.RunSQL strTotalShips
>>>>>> Me!txtWk1TotalS hips = strTotalShips

>>

>>>>>>>Thanks for the help in advance.

>>

>>>>>>>Troy Lee

>>

>>>>>>RunSQL, as well as Execute, are used for action queries (update/delete,
>>>>>>etc).

>>

>>>>>>You could set it to a recordset
>>>>> dim r as DAO.recordset
>>>>> Dim strTotalships as string
>>>>> 'surround dates in #
>>>>> strTotalShips = "SELECT RMAs.*, RMAs.[QAreviewed Date] " & _
>>>>> "From RMAs " Where RMAs.[QAreviewed Date] " & _
>>>>> "Between #" & Me![txtWk1BegDate] " & "# AND #" & _
>>>>> Me![txtWk1EndDate] & "#;"
>>>>> set r = currentdb.openr ecordset(strTot alShips,dbopens napshot)
>>>>> 'move to last record to get true recordcount
>>>>> If r.RecordCount 0 then r.movelast
>>>>> Me!txtWk1TotalS hips = r.RecordCount
>>>>> r.close
>>>>> set r = Nothing

>>

>>>>>>Bohttp://www.youtube.com/watch?v=6vwXAge xoto

>>

>>>>>Salad,
>>>>>Thanks for the help. It works perfect. I have another question.

>>

>>>>>I want to use the same parameters for the query except that I need to
>>>>>examine the recordset and count two different things in it.

>>

>>>>>One, I need to count all the records where one of the fields = "2017"
>>>>>and return that count to a text box on the form. I then need to count
>>>>>all the other records that are not 2017 and output that to another
>>>>>text box. Can you help with this portion of the SQL statement?

>>

>>>>>Thanks.

>>

>>>>>Troy

>>

>>>>There's multiple ways of doing it. I might use a totals query. Don't
>>>>know if FldName is a numeric or character value...if character then
>>>>surround by a ', else no '. Ex:

>>

>>>strSQL = "SELECT Count([FldNm]) AS CntAll," & _
>>> "Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017 From RMAs " & _
>>> "Where ..."
>>>>set r = currentdb.openr ecordset(strSQL ,dbopensnapshot )
>>>>'force a zero if count is blank with NZ
>>>>me.Is2017 = NZ(r!Cnt2017,0)
>>>>me.Not201 7 = NZ(r!CntAll,0)-NZ(r!Cnt2017,0)
>>>>r.close
>>>>set r = nothing

>>

>>>>Three Birdshttp://www.youtube.com/watch?v=rnDrbag Ym24

>>

>>>Sorry about my last reply. I saw you included provisions for my
>>>question in your last code submission.

>>
>>I could have used
> strSQL = "SELECT Sum(IIf([FldNm]='2017',1,0)) AS Cnt2017," & _
> "Sum(IIf([FldNm]<>'2017',1,0) ) AS CntNot2017 From RMAs " & _
> "Where ..."
>>
>>

>>>One more question... How do I return the values me.Is2017 and
>>>me.Not2017 to my form's text boxes?

>>
>>Change my Me.Is2017 and Me.Not2017 to their respective text box names in
>>your form? You didn't supply their names.
>>
>>Iranhttp://ca.youtube.com/watch?v=uUjIA3R t7gk
>>
>>
>>
>>

>>>Troy

>
>
Never mind Salad. I got your second piece of code to work. I like it
much better anyways. Thanks you so much.
>
Troy

A couple of suggestions. In the future, don't create field names in
your tables with spaces. Sure, you can create/use them...they're simply
more pain than they are worth. Capitalize the first characther of each
word in a field instead. You don't need [] either in that case.

If you use a function like Count(), you need wrapping (). I think that
might have been an error.

If you find a query doesn't work in code.
Dim s As String
s = "Select * From Table1"
then you might want to do a
Dim s As String
Debug.Print s
s = "Select * From Table1"
and then get to the debug/immediate window and copy the value of S to
the clipboard. (Afterwards, remove the debug line when debugging is
complete) Then create a new query; Query/New/Design/Close and then
select View/SQL from the menu and paste the SQL string into the query
builder and run it. I think you'll see what causes the error.

BTW, you're welcome.