query to variable

[MartijnR]

hi,

I'm trying to use the outcome of a query in code, so I can use a loop to add the outcome to earlier fields to 1 variable. However, the help of ms-access cannot help me on this one.

example:

field1 field2
4 test@what.com
4 why@doesntthiswork.duh

has to go into 1 variable, which has the value : test@what.com; why@doesntthiswork.duh

Can anybody please help me with this?

Thanks,
Martijn

 

[raskew]

I'm a little unsure from the example re output from your query.

If [field1] and [field2] are on the same query record, then create a calculated field, e.g.

MyField: [field1] & "; " & [field2]

 

[MartijnR]

Perhaps I didn't explain it enough. Field1 and Field2 are query fields.

The query can have a number, none or 1 record(s). It are the values in Field2 I would like to add (and keep adding) to a variable.

The ; would be something like & ";" in VBA (would be added in loop in the code itself).

So if the query has 4 records and Field2 would have the results : 1, 2, 4, 9 (something totally different then the emailadresses, it's the way to do this thats important to me), the variable would have to have a value of 1249 (or 1;2;4;9) if ; is added in the code.

 

[raskew]

Copy/paste to a new module in Northwind, then from the debug window type:
? stringem("Employees", "LastName", ", "

This should work equally against both tables and queries.

Function StringEm(ptblName As String, pfldName As String, _
Optional xx As String) As String
'*******************************************
'Name:      StringEm (Function)
'Purpose:   Build a string of field names
'           displayed in a record set
'Author:    raskew
'Called by: Function StringFind()
'To test:   From Northwind debug window
'           ? StringEm("Employees", "LastName", ", ")
'*******************************************

Dim db As Database, rs As Recordset
Dim strSQL As String, strHold As String
Dim n As Integer, i As Integer
xx = IIf(xx = "", "", xx)
Set db = CurrentDb
strSQL = "SELECT distinct " & pfldName & " FROM " & ptblName _
& " ORDER BY " & pfldName & "; "
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
  For i = 1 To n
     strHold = strHold & rs(pfldName) & xx
     rs.MoveNext
  Next i
   strHold = Left(strHold, Len(Trim(strHold)) - Len(Trim(xx)))
  StringEm = strHold
End If
rs.Close
db.Close
Set db = Nothing
End Function

 

[MartijnR]

In Northwind this example works and it does what my db needs to do. However when I copy/paste the code into my db (in a module or as code behind a form), I get a compile error on 'Dim db as Database', 'User-defined type not defined'.

Do you know why this happens? Do I need to declare something somewhere?

 

[raskew]

What version are you using? Is all your work being done on the same computer?

It sounds like a reference problem, but I'm a bit baffled that the code will work with Northwind but not another db.

 

[MartijnR]

i'm using w2kpro and ms-access2k (9.0.2720)

the northwinddb and my own db are on the same computer, so that probably isn't the problem

 

[MartijnR]

anybody?

Do I need to make a declaration (or something else) to get :

dim db as database

working?

 

[Rich]

I think it's something like
Dim db As DAO Database

 

[MartijnR]

I have checked it briefly, but when I type dim db as

I get a dropdownbox, and daodatabase isn't in there. (typo?)

And allso, why does as database work in northwind and not in my own db? northwind shows that access can handle it.

 

[Rich]

I'm not sure which version you're using, but this sounds like the usual reference problem, this site should help
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html