multiple values from a temp table

[1jet]

Hi all,

Been stuck on this for a while...was hoping for some more help.
I have a temporary table like such

TS_Date Project_Name Hours_Worked
19/10/2008 Dalkeith 0
20/10/2008 Dalkeith 4
21/10/2008 Dalkeith 4
22/10/2008 Dalkeith 4
19/10/2008 East 0
20/10/2008 East 4
21/10/2008 East 4
22/10/2008 East 4

Now if I wanted to find the Project_Name for criteria TS_Date = 22/10/2008, I cannot use Dlookup bcos there's going to be 2 results.
Dalkeith and East (obviously).

How would I get these two values into two separate variables?

Cheers

 

[gemma-the-husky]

dlookup will find the first instance only, as you say

depending what you need to do, have a query selecting the items with a matching date, and use that as a bulk query.

if you need to process the query rows one at a time, in a way you cant do with a bulk query, then you will need to iterate a recordset

pseudocode for this would be

while not recordset.eof
process the recordset row
recordset.movenext
wend

 

[Satal Keto]

Hello again

My suggestion is to use DAO for example

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    dim ProjectDate As Date
    dim ProjectName As String
    dim ProjectHours As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblProjects WHERE Project_Name = 'Dalkeith'")
    
    If rs.EOF = False Or rs.BOF = False Then
        'results returned
        rs.MoveFirst
        While rs.EOF = False
            ProjectDate = rs("TS_Date")
            ProjectName = rs("Project_Name")
            ProjectHours = rs("Hours_Worked")
            msgbox "Date: " & ProjectDate & " Name: " & ProjectName & " Hours: " & ProjectHours
            rs.MoveNext
        Wend
    Else
        'no results returned
    End If
    
    rs.Close
    set rs = Nothing
    db.Close
    Set db = Nothing

What the

while rs.EOF = false
    rs.MoveNext
wend

does is it loops through each of the records in the record set you had returned

Hope this helps

P.S. Sorry for the mistake, realised right after posting

P.P.S I just realised I used a function in my code which is for internal use for my company, so I have changed my code again to work without the function. My appologies

 

[tranchemontaigne]

I'm a little confused by your post. Do you want your output as two separate records, or two separate values in a single record?

Satal Keto's post forms an elegant method for doing this work, but you will have to edit it a little if you want your output in a single record.
________
PLYMOUTH LASER HISTORY

 

[1jet]

Tranchemontaigne, I'd like my output to be as two separate records in this case.
As my criteria would be where TS_Date = 22/10/2008.

It is possible that this criteria may give even 3 or 4 results. If that happens, I'd need also need those 3 or 4 results as separate records (preferable into string variables).

I can change the temporary table to omit the "Hours_Worked" field, but that would still produce basically the same table and same problem.

TS_Date Project_Name
22/10/2008 Dalkeith
22/10/2008 East

Still need to get Dalkeith and East into their own string variables each.


Satal, whilst I appreciate your help, my original table is the result of a "make table" based on an employee. So this table will have varying results hence varying project names.

The only sure thing I have is the TS_Date criteria.


Gemma I'm working on your suggestion right now.

 

[Satal Keto]

Satal, whilst I appreciate your help, my original table is the result of a "make table" based on an employee. So this table will have varying results hence varying project names.

The only sure thing I have is the TS_Date criteria.

Then you just change the SQL to search by TS_Date

Gemma I'm working on your suggestion right now.

Unless I am miss understanding Gemma's suggestion it is the same as mine.

I would personally suggest trying to learn how to use recordsets as they are very useful
They may not be the only solution for this but they're the best solution I can think of for this.

 

[1jet]

Sorry I just realised after posting that it's the same.
Cheers buddy!

...and yeah I'm gonna have to go into learning about record sets...

 

[1jet]

Alright Satal,
I've done what I could next and started reading on ADO's and connecting to Access files. There is however already a problem. When I try make an ADO connection to my Access project, there's a message saying that my database is in a state that prevents it from being opened or locked.

Any suggestions?

If I've made a mistake with my code, it's pasted below...cheers

Sub ConnectionExample()

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source =" & CurrentProject.Path & _
          "\GKA Timesheet.mdb"

conn.Open
MsgBox "Connection was opened."

conn.Close
MsgBox "Connection was closed."

Set conn = Nothing

End Sub

 

[Satal Keto]

I have to admit I am not familiar with ADO, I only use DAO.

 

[boblarson]

If connecting to the same database, you don't need to use the connection string. You set the connection like this:

Sub ConnectionExample()

Dim conn As ADODB.Connection
Set conn = [b][color=red]CurrentProject.Connection[/color][/b]

conn.Open
MsgBox "Connection was opened."

conn.Close
MsgBox "Connection was closed."

Set conn = Nothing

End Sub

But you don't have to do that even. You can just use

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "YourQueryHere", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[i]...Your other code here...[/i]


rst.Close

Set rst = Nothing

 

[1jet]

You guys don't know how much I appreciate your help with this.
It's helped out a s**tload

Just out of curiosity though, I'm stunted by this code by Sato

If rs.EOF = False Or rs.BOF = False Then

What's this mean?

 

[boblarson]

You guys don't know how much I appreciate your help with this.
It's helped out a s**tload

Just out of curiosity though, I'm stunted by this code by Sato

If rs.EOF = False Or rs.BOF = False Then

What's this mean?

If you are not at the end of the recordset or at the beginning of the recordset, but I haven't seen that one done like that before so maybe they will chime in and explain why they would use it like that.

I typically see it done (and use it) like

If Not rs.BOF AND Not rs.EOF (which means if you aren't at the beginning AND end of the file - which means there are no records).

 

[Satal Keto]

Just out of curiosity though, I'm stunted by this code by Sato

If rs.EOF = False Or rs.BOF = False Then

I have to admit I am not 100% certain why this code is used, its just how I was taught to do it, I will ask my boss next time I see him (which I have to admit isn't for a week and a half as Im on holiday ), although I get the feeling I might get the same answer as I just gave

I would assume that it is because of an assumption that a recordset which has been returned could have the pointer at the beginning of the recordset or at the end of the recordset. But assuming the recordset has at least one record they can't both be true.
I guess it would probably be useful to find out at what point does the pointer get assigned and where it gets assigned.

I also suppose that you could use something like

If rs.RecordCount > 0 Then

Which should avoid the question about the choice of logical operator.

Hope this has helped 1jet

 

[1jet]

Cheers fellas, you've all been a great help!

 

[1jet]

Sorry smee again.

As you all know, with this issue I just had, I created a temp table (via MAKE TABLE) then retrived records via ADO recordsets.

Sorry for my n00bness, but as I was looking through my code, I thought I was able to omit creating the temp table, and use "recordsets" instead.
I think if that's possible I can use multiple recordsets and save memory resources so Access will not have to create these temp tables...correct?

 

[tranchemontaigne]

If rs.EOF = False Or rs.BOF = False Then

This code is used to handle an empty recordset. If you have an empty or null recordset the recordset object does exist, but many recordset methods will cause runtime errors (e.g. .movefirst, .movenext, move previous, .movelast)

This line of code is checking and if the recordset is not null (i.e. not BOF and not EOF), then it will execute a block of code nested within your IF loop.

Personally, I like to use a loop immediately after the recordset is declared. This works better for backwards compatibility. Within VB, as soon as you define a recordset the pointer is placed at the beginning of the recordset. This prevents the need to use a .movefirst call that will generate runtime errors as just mentioned.

lnN = 0
do until rs.EOF
lnN = lnN + 1
rs.movenext
loop

if lnN > 0 then
rs.movefirst
...
end if

[/quote]
________
Toyota tsusho