Query Function

[BlueWater]

Access 2007

I am trying to create a user defined function to parse name fields in a query. The code below is a simple task used to establish quick and simple test code. The function works, but will not move beyond the first record. It executes the first record and appears to use the first record as though copy and paste… Is there a way to step through each record?

My query field is: xName: Test2()
My code:
Function Test2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSample1")

If Not (rst.BOF And rst.EOF) Then
Test2 = StrConv(rst![lastname], vbProperCase)
Else
rst.MoveNext
End If

rst.Close
Set db = Nothing
End Function

 

[SimonB1978]

Hi,

I'm not too sure what you want to do here. Do you want to list fields from a table but have the name field formatted? If so you could simply write:

SELECT StrConv(name_field, vbPropercase) AS My_Name, column_1, column_2 [...] FROM the_table

That will format your name field right away.

If that's not what you want give a bit more info so we can help better.

Simon B.

 

[gemma-the-husky]

this is wrong

If Not (rst.BOF And rst.EOF) Then
Test2 = StrConv(rst![lastname], vbProperCase)
Else
rst.MoveNext
End If

to step through all the records use this sort of thing

while not rst.eof
  Test2 = StrConv(rst![lastname], vbProperCase)
   'do something with it
   rst.MoveNext
wend

 

[BlueWater]

Simon B:
Thanks for your reply –sorry for the confusion in terms of my initial description. Basically, as a learning experience, I want to use VB code (function) for some query tasks rather than using the expression builder. The expression builder (builder) is fine for some tasks, but from my view it is not easy to deal with after a couple of line of code, and I am sure others may differ but that is my opinion.

The VB code (initially listed) was my attempt to create a test function for use in a query rather than builder code. My intent was to use something simple to see and understand how a function works with a query. I am sure it is obvious that I am new to Access and struggling with VB code, nevertheless, success with simple tasks are the building-block and rewards for larger more complex tasks (inexperience and self motivation has its price).

With that in mind all I want to do is, use a function in a query, to step through each record of a column. Getting that task functional will help going forward with additional code. The code listed “Test2 = StrConv(rst![lastname], vbProperCase)” was simple but also visual, something to get quick results.