How do I RecordSet

[Valentine]

Dim dbCurr As DAO. Database
Dim rsLate as DAO.Recordset
Dim lateStr, cidrThreatStr as String
Dim tmpCIDRCount, tmpJCRBValid, tmpRASA, tmpRASAHold, tmpCRIB, tmpCRIBHold, tmpDevHold, tmpTandE, tmpTandEHold, tmpReadyforAccept as long

    response = MsgBox("Have you selected the threat topic and information cutoff date?", vbYesNo + vnCritical + vbDefaultButton2, "Generate CIDR Snapshot Confirmation")
    If response = vbNo Then
        Exit Sub
    End If
    
    Set DbCurr = CurrentDb()
    lateStr = "Select CdgrToCidr.[CDGR ID#], FulfillmentGroup.[Fulfillment Group], CnfToThreat.[Threat Adversary], Cnf.Status, Cnf.[Need By Date] " _
              & "FROM (((Cnf INNER JOIN CnfToCdgr ON Cnf.[CNF ID#] = CnfToCdgr.[Cnf ID#]) LEFT JOIN CnfToThreat ON Cnf.[CNF ID#] = CnfToThreat.[Cnf ID#]) " _
              & "INNER JOIN CdgrToCidr ON CnfToCdgr.[CDGR ID#] = CdgrToCidr.[CDGR ID#]) INNER JOIN FulfillmentGroup ON CdgrToCidr.[Fulfillment Status] = FulfillmentGroup.[Fulfillment Status] " _
              & "GROUP BY CdgrToCidr.[CDGR ID#], FulfillmentGroup.[Fulfillment Group], CnfToThreat.[Threat Adversary], Cnf.Status, Cnf.[Need By Date] " _
              & "HAVING ((CdgrToCidr.[Fulfillment Status] Not Like 'Rejected*') AND ((Cnf.Status) Like 'JCRB Validated*'));"
    cidrThreatStr = Me.cmdSelThreatTopic.Value
    If (cidrThreatStr = "Other" Or cidrThreatStr = "Agnostic" Or cidrThreatStr = "All") Then
        lateSTr = lateSTr & " And CnfToThreat.[Threat Adversary] Not Like 'CHN' AND " _
                  & "CnfToThreat.[Threat Adversary] Not Like 'IRN' AND " _
                  & "CnfToThreat.[Threat Adversary] Not Like 'PRK' AND " _
                  & "CnfToThreat.[Threat Adversary] Not Like 'RUS' AND " _
                  & "CnfToThreat.[Threat Adversary] Not Like 'VEO' AND " _
    Else
        lateStr = lateStr & " AND CnfToThreat.[Threat Adversary] = '" & cidrThreatStr & "'"
    End If

So this is my start. I want to run through and count the items by their fulfillment group status and record it in a table i created so I can look at the numbers for a later item.

I was told i can create a record set to do this but i don't know how to do that.

 

[bastanu]

First of all in need to fix your variable declarations: Dim lateStr, cidrThreatStr as String only the last one is a string the first is a variant; same issue with the ones you try to declare as long.
Dim tmpCIDRCount as Long, tmpJCRBValid as Long, .... tmpReadyforAccept as long

Also, usually you do not store counts in a table as they can quickly get incorrect, you calculate them when you need them (in a query\report, etc.). Instead of using recordsets you can probably use domain aggregate functions such as dCount, dSum, etc.

Finally to answer your question you would need to use the SQL string you defined in a new line:
Set rstLate=dbCurr.OpenRecordset(lateStr)

Cheers,

 

[Pat Hartman]

Not Like 'IRN'

There is absolutely no reason to use LIKE if you are not using any wild cards. LIKE is intended to be used to search for partial strings. You do NOT have a partial string, so you should be using = rather than LIKE unless you "like" forcing the query engine to inefficiently read the table contents row by row instead of using an index.

 

[Cronk]

To answer your basic question, you can open a recordset of all the records satisfying your criteria by the code

set rsLate = currentdb.openrecordset(lateStr)

However in addition to the issues raised by the others so far, there is a further problem that your initial lateStr contains a SQL terminating ; and that will interfere with the other criteria and give an error

 

[Valentine]

Thank you for all the comments I will change how my variables are DIMd to not have them on 1 line. I still don't know how to do what they want me to accomplish. In the SQL there is a field "Fulfillment Group" where there are 16 different options that a CNF could be in at a given time. I want to add up the number of each option and determine its lateness dependant on the date the button is pushed. Then i need to show it in a report. I am EXTEMELY new to this and have no clue how to accomplish this task.

 

[Pat Hartman]

I fixed up your SQL a little. I'm not sure if I got it right. For starters, I removed the HAVING and replaced it with a WHERE. That meant I had to move the construction of one sub section out of the main build for lateStr and make an additional variable. Also note the In()

Dim strThreat As String
    
    If Me.cmdSelThreatTopic = "Other" Or cidrThreatStr = "Agnostic" Or Me.cmdSelThreatTopic = "All") Then
    strThreat= " And CnfToThreat.[Threat Adversary] Not In('CHN','IRN','PRK', 'RUS', 'VEO')
    Else
    strThreat = lateStr & " AND CnfToThreat.[Threat Adversary] = '" & Me.cmdSelThreatTopic & "'"
    End If
        

lateStr = "Select CdgrToCidr.[CDGR ID#], FulfillmentGroup.[Fulfillment Group], CnfToThreat.[Threat Adversary], Cnf.Status, Cnf.[Need By Date] " _
              & "FROM (((Cnf INNER JOIN CnfToCdgr ON Cnf.[CNF ID#] = CnfToCdgr.[Cnf ID#]) LEFT JOIN CnfToThreat ON Cnf.[CNF ID#] = CnfToThreat.[Cnf ID#]) " _
              & "INNER JOIN CdgrToCidr ON CnfToCdgr.[CDGR ID#] = CdgrToCidr.[CDGR ID#]) INNER JOIN FulfillmentGroup ON CdgrToCidr.[Fulfillment Status] = FulfillmentGroup.[Fulfillment Status] " _
          & WHERE((CdgrToCidr.[Fulfillment Status] Not Like 'Rejected*') AND ((Cnf.Status) Like 'JCRB Validated*'))" _
          & lateSTr = lateSTr &  strThreat _
              & "GROUP BY CdgrToCidr.[CDGR ID#], FulfillmentGroup.[Fulfillment Group], CnfToThreat.[Threat Adversary], Cnf.Status, Cnf.[Need By Date] "

Given that, I still don't know if this will work for you. Personally, I don't build static (not changing) SQL strings in VBA. I always use QBE because it is easier to test. To test this string, you need to put a stop in the conde on the line AFTER the string is built. I don't know what that is since your code stops with the string build. Once the SQL string is built, print it to the debug window. then copy it and paste into the QBE in SQL view and test. This is exactly why I do not do this. I'm not sure why you are building the SQL this way. You can still modify the where clause by in your code creating a query = "Select * from query1 WHERE " & strThreat -- OR instead of a nested IF, you can use an IIf() in the QBE to make the criteria optional. That gets everything into a single querydef which will probably make the rest of what you want to do much easier.

Picking bits out of your post - you do NOT need to store counts anywhere. You can use a query in place of a table pretty much anywhere.

 

[Valentine]

Thank you, yeah I stopped at that point in the code because i didnt know how to go forward. I am soo nw I dont know what you mean when you say QBE I only did the code this way so far is because thats what i was taught. If there is a better way please help me I am starting to get into this field and have a few 40 hour courses coming up but not until April so any new ways to do things will just help me grow.

 

[MajP]

QBE is the "query by example" or "query development environment". It is the graphical user interface that lets you build queries without having to write the SQL code. You drag the table into it and connect them with lines. Then you can pick the fields, sort orders, and criteria. It builds the SQL code. Or you can write it youself.

 

[Valentine]

oh ok, I don't actually know how to write the SQL I just create the query in Access and open the SQL viewer and copy paste that over to the code.

 

[Pat Hartman]

Just leave the query as a querydef. Access defaults to a HAVING clause unless you specify WHERE.

Just FYI, the WHERE clause is applied to the raw data BEFORE any aggregation takes place. The HAVING clause is applied as the last step AFTER data is aggregated so HAVING is ONLY necessary when your criteria is looking at an aggregated value. For example, if you want only orders where the sum of the details is > $1000, that would be HAVIING since you don't know if you want the row until after you calculate the sum. However, if you want only orders for Florida, that would be a where since you know the value you are looking for and the where allows you to exclude all orders EXCEPT those from Florida before doing the aggregation.

So, used correctly, the criteria makes the query more efficient. To generate a WHERE clause, use WHERE rather than Group By as the aggregation method for the columns with your criteria. The HAVING is generated automatically when you apply criteria to columns that use Sum, First, Avg, etc. And even Group By if you don't know any better.

Also, take a look at the In() clause where I simplified the bunch of OR values.

 

[Valentine]

Oh nice i didnt know that was a thing I have been doing those long exceptions all this time in regards to the threat adversary.

Now back to the problem, I don't think i have explained it properly and with my code above it misrepresented what i want. I have thousands of these CNFs in my system that all represent a requirement and they go through 16 different steps of approval. Each CNF has a "need by date" in them and I am trying to create a print out of the sum of each position by threat. What i mean is a user selects a threat from a drop down and then clicks the button, the button is supposed to pull ALL CNFs that apply to that threat and then i want it to count them up for a total as well as by date. What I mean by date is when the button gets pushed would be current date and that should relate to the need by date of each CNF to discern if it is 1-30 days, 31-90, 91- 1 year, and over a year late so I would have a chart in a subform that looks kinda like the following.

Stage	Total	On Time	1- 30 days	31- 90 days	91 - 1 year	over a year
Validation	15	3	7	0	2	3
Test	7	1	5	0	0	1
Evaluation	6	2	1	1	1	1

I have created the subform with text boxes but don't know how to bind the text boxes since I am not using a table to put the totals in (unless I have to). Plus I don't know how to write the code to count each section and apply it to the subform.

 

[Valentine]

Thought on an attempt. I am going to try a loop were it sets tmpValidation, tmpTest, so no and so on, and inside them I would have
= sum(IIF(Between Date() and Date() - 30), "[name of query]", [Fulfillment Group] = 'Validation')

Would that work? Like would it loop through and put in the tmp variable the count of every validation CNF in the date window?

 

[Pat Hartman]

You need to create a query that buckets the items by time.. Then create a crosstab query. Use the crosstab query as the recordsource for the report.
I would start with a query that calculates the difference in days. Just because that makes the bucketing cleaner since you have a "days" field and don't have to keep repeating the calculation in the IIf()

Select Stage, DaysLate, IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) As Bucket From FirstQuery;

Use this query in a crosstab.

 

[Valentine]

In my head I can see how this works but i have never done any query that isn't just clicking the "Query Design" button in the create tab. How do I do these queries?

 

[Valentine]

I have figured out how to do the crosstab query and am now going through my database and playing with this new found knowledge, thank you. I cannot for the life of me figure out how to do the first query you said, the bucket one with the iif statements. Am I making a normal query and inputting that in the criteria of the query? or is that supposed to be apart of the

 

[Pat Hartman]

I forgot the from clause so I updated the earlier sample.

Let me back up to the first query
Select Stage, Date() - YourDate As DaysLate From YourTable;

Save this querydef.

Then use the other query to bucket it. In the earlier post, I called this query "FirstQuery". Use names that will make sense to you. The earlier sample is "SecondQuery" and that is the one you would use as the source for the crosstab. I'll repeat it here for convenience:

Select Stage, Count(*) As NumLate, IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) As Bucket From FirstQuery
Group By Stage, IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) ;

Once you calculate the daysLate, the second query assigns the bucket. Then the crosstab makes it look like you want.

 

[Valentine]

I forgot the from clause so I updated the earlier sample.

Let me back up to the first query
Select Stage, Date() - YourDate As DaysLate From YourTable;

Save this querydef.

Then use the other query to bucket it. In the earlier post, I called this query "FirstQuery". Use names that will make sense to you. The earlier sample is "SecondQuery" and that is the one you would use as the source for the crosstab. I'll repeat it here for convenience:

Select Stage, Count(*) As NumLate, IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) As Bucket From FirstQuery
Group By Stage, IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) ;

Once you calculate the daysLate, the second query assigns the bucket. Then the crosstab makes it look like you want.

I am sorry if I am harping on this but I have only received OJT in coding with NO schooling until the near future so I still don't know how to do this. The need by date that the "dayslate" is ordered from is in the CNF table and the stages are in another table that is linked through the CNF #. Can you explain it idiot proof to me, cuz I am kinda an idiot in this right now, I have only actually been coding for like 3-4 months now so I dont really know a lot yet.

 

[Pat Hartman]

Start with the first query

Select Stage, Date() - YourDate As DaysLate From YourTable;

Open the QBE and select the tables you need. If Stage and the date you need are in different tables select both and create the join line.
Select Stage, Select the date you need.
Switch to SQL view and change YourDate to

Date() - YourDate As DaysLate

Save it. Test it

CReate the second query. This time choose the first query rather than tables. Select the two fields. Switch to SQL view and Replace DaysLate with IIf(DaysLate >=0, "On Time", IIf(DaysLate < 31, "1-30 Days", IIf(DaysLate < 90, "31-90 Days", IIf(DaysLate < 366, "91-1 year", "Over a year")))) As Bucket

Go back to QBE view. Change the type of query to TOTALS. That adds an additional line to the grid.
Under Stage and the calculated Bucket will be "Group By"
Add a third column it is calculated so type it this way:

NumLate:Count(*)

Then in the grouping row, change the method to Expression if you need to. Save. Test

Once it works, use the Wizard to build the Crosstab query from the second query.

 

[Valentine]

ok i think im going to throw a wrench into this, I want to figure out how late something is based on the current date. Like I want an event to run from a combo button to do calculations. So basically when the user selects the appropriate threat and then pushes the button the event should calculate the difference in dates from the need by date from the CNF and the current calendar day then put them in the appropriate bucket. I tried to follow what you posted and when you say "Select the date you need" I am not specifically selectign dates I want to see how old they are from the current day and place them in the day old buckets.

 

[Cronk]

"Select the date you need"

Read that as "Select the date field required" Pat used generic name YourDate as the field name. Date() is the current day as set by your PC. If the value of the field YourDate for a particular record,

Date() - YourDate

gives the number of days late