Not sure where to start.

[KadeFoster]

Hey all,

Has been a while since i have been on here. I am tying to create a weekly report. So today -7.

TASKID	Status	Updated
130	Scaling	1/12/23
130	Completed	2/12/23
131	Completed	25/11/23
132	Completed	2/12/23
134	Scaling	22/11/23
134	Scaling	4/12/23
134	Completed	6/12/23

I want all records for the TASKIDs where the last entry has STATUS as "Completed" and UPDATED is within TODAY()-7.

So all entries for that TASKID and others that fit the criteria.

Suggestions or ideas, i struggle with queries.

 

[arnelgp]

create a query:

select taskid, status, [updated] from
yourTableName
where
[updated] between Date()-7 And Date()
And
[status] = "updated";

 

[Minty]

@Uncle Gizmo I hope that was a poor ChatGP generated answer, as that is the messiest, most long winded way to make a SQL string eVah !

 

[Minty]

Fair enough - I use

strSQL = "SELECT "
strSQL = strSQL & " blah blah "

As I find the continuation method difficult to read and debug.

 

[Gasman]

I tended to build my sql string like that, but just the one variable. Plus I always put the space at the beginning of the new line, so you do not have to scroll to the far right to see if one is there, though the subsequent debug.print would reveal that.

 

[CJ_London]

And you wood have a slightly different string which would show current orders:-

does it pick up typo's?

 

[NauticalGent]

It's pile-on Tony day, I see.... Good thing he is thick-skinned!

And I am going to go out on a limb here and say the OP has no idea what you are talking about and was hoping someone would show how to do this in the QBE grid:

 

[ebs17]

The first consideration might be whether it even makes sense to break up a CONSTANT SQL statement into VBA lines, or whether to store your SQL code in a query object and thus use a stored query that you call with the name.
Arguments: Use of the execution plan, can be tested directly, VBA code is shorter or even unnecessary.

 

[KadeFoster]

It's pile-on Tony day, I see.... Good thing he is thick-skinned!

And I am going to go out on a limb here and say the OP has no idea what you are talking about and was hoping someone would show how to do this in the QBE grid:

View attachment 111313

Yeah a little but the alternatives are interesting as well. I didn't think this would get replied to quickly. OK i'll give this/these a go and see how it works, and try to understand whats going on.

 

[isladogs]

When writing query sql in code, I normally do so as a single block with line continuations as I find that easier to read and debug.

However, there are two types of situation where I would split it using string variables:
1. The sql is so long that I get 'a too many line continuation error' - if so I'd use @Minty's approach to fix
2. Where the various elements of the query are entered at runtime by the user e.g. by selecting from choices in combos etc.
For example, see https://www.isladogs.co.uk/multiple-group-filter/index.html

In the latter case, I use a fifth way (if I haven't lost count...) building the sql for each clause ...
strSQL = strSELECT & strFROM & strWHERE & strGROUPBY & strHAVING & strORDERBY

Doing that is of course similar to the idea shown in post #3 but it does have the advantage of meaningful string variables

 

[The_Doc_Man]

Colin's method (building the various clauses piecemeal and concatenating after the parts are built) was what I did for one of my more ambitious queries. Turns out that some of the parts were re-usable so that I didn't have to rebuild the whole query string at the lowest level every time.

 

[isladogs]

Colin's method (building the various clauses piecemeal and concatenating after the parts are built) was what I did for one of my more ambitious queries. Turns out that some of the parts were re-usable so that I didn't have to rebuild the whole query string at the lowest level every time.

That's one of the main reasons I use that approach for complex queries. For examples of this in use, see my example app Multiple Group & Filter (isladogs.co.uk)

In fact, I take it a step further by concatenating the output from multiple combos, textboxes & option groups to create the WHERE filter

Private Sub GetRecordSource()

strWhere = strWhere & strYear & strTG & strGender & strFSM & strCOP & strLowEn & strLowMa _
    & strPupilPremium & strEAL & strFirstLang & strEthnicity & strLEACare

'combine with strSelect (set in Form_Load) and strOrderBy (depends on user choice)
strRecordSource = strSelect & " WHERE " & strWhere & " ORDER BY " & strOrderBy & ";"

Me.RecordSource = strRecordSource

CheckFilterFormat     'used to add green shading to filtered fields
Me.Requery
GetListTotal     'shows the recordset count and a summary of the filters used

End Sub