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,

 

[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.

 

[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.

 

[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?

 

[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

 

[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.

 

[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

 

[Valentine]

Select [JCRB Slides Query - Late CIDR].[Fulfillment Group], [JCRB Slides Query - Late CIDR].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, Count(*) As NumLate
FROM [JCRB Slides Query - Late CIDR]
GROUP BY [JCRB Slides Query - Late CIDR].[Fulfillment Group], [JCRB Slides Query - Late CIDR].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"))))

I think i did something wrong cuz I am getting an error. Undefined Function '[JCRB Slides Query - Late CIDR].IIf' in expression

 

[Gasman]

You need to fully qualify each test with the tablename I expect?
Might use the Switch() function?

MS Access: Switch Function

This MSAccess tutorial explains how to use the Access Switch function with syntax and examples. The Microsoft Access Switch function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE.

www.techonthenet.com

 

[SHANEMAC51]

Итак, это мое начало. Я хочу пробежаться и подсчитать элементы по их статусу группы выполнения и записать его в таблицу, которую я создал, чтобы я мог посмотреть на цифры для более позднего элемента

there are several compilation errors in your code and there are undeclared variables - the code will not work

in addition, it is absolutely unreadable - the width of the lines is much larger than the width of the screen

a few seconds spent on splitting long lines into short ones, into the screen) in the query editor will allow you to bring it into a readable form and significantly speed up the debugging of the task.

Dim dbCurr As DAO.Database
Dim rsLate As DAO.Recordset
Dim sq, sw As String   ''''''''''''''''''
Dim tmpCIDRCount, tmpJCRBValid, tmpRASA, tmpRASAHold, tmpCRIB, tmpCRIBHold, tmpDevHold
Dim tmpTandE, tmpTandEHold, tmpReadyforAccept As Long

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