Re: SQL help

troy_lee@xxxxxxxxxxx wrote:

On Jun 6, 2:01 pm, Salad <o...@xxxxxxxxxxx> wrote:

troy_...@xxxxxxxxxxx wrote:

On Jun 6, 10:06 am, Salad <o...@xxxxxxxxxxx> wrote:

troy_...@xxxxxxxxxxx wrote:

On Jun 5, 2:50 pm, Salad <o...@xxxxxxxxxxx> wrote:

troy_...@xxxxxxxxxxx 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!txtWk1TotalShips = 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.openrecordset(strTotalShips,dbopensnapshot)
'move to last record to get true recordcount
If r.RecordCount > 0 then r.movelast
Me!txtWk1TotalShips = r.RecordCount
r.close
set r = Nothing

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

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.openrecordset(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=rnDrbagYm24

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=uUjIA3Rt7gk




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.

.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Use ADO to retrieve data from Access Parameter Query
    ... I want to run an existing Access *Parameter* Query and pass 2 params to the ... Dim Param1 As ADODB.Parameter ... > Here is a function that returns a recordset based on a SQL statement. ... > Public Function RunQuery(ByVal strSelect As String, ...
    (microsoft.public.excel.programming)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
Loading