i need to add this code to one of the feilds in my access report:
Public Function GetID(Expr3 As Integer) As Integer


If Len([Expr3]) = 3 Then
Expr3 = [Expr3] & "00000"
Else
If Len([Expr3]) = 5 Then
Expr3 = "470502" & [Expr3]


End Function

what i'm trying to do here is get the length of the feild in the report and depending on that i want to modify that feild by adding the proper prefix or suffix. i wrote the code in the code builder but it doesn't seem to be doing anything. i don't think that my report or my field even know about this code. how do i attacd or match or add that peice of code to my report? thank you

p.s i don't really know how to use the code builder so i would apreciate a detailed answer thank you

You can put this code in a module, and then put this in the field in your report

=GetID()

What is "Expr3"? Is that a field on your report? If so, then you would put:

If Len([Reports]![rptYourReportName]![Expr3]) = 3 Then etc. since the module will need to know which report it is getting Expr3 from.

This should get you started.


mmcdonal

thank you for your response
i want to ask you, does my code make sense to you? i mean do you see any problems in it because when i was testing it in the immidiate window it always gave me 0 as the result and the same thing when i tried it in a query. i don't understand why it's giving 0 when it is supposed to take the a number and concatenate it to either"00000" or "470502" depending of the lenght of the given number.
the modifications are made on the given field so they new number should apear there, or at least how i understand it. but why is it returning 0?

Part of the problem is that you want to concatenate (string) with intgers. So what you want to do is convert the integers to strings, concatenate, and then convert back to integers.

'=====
Public Function GetID(Expr3 As Integer) As Integer

Dim strExpr3 As String

strExpr3 = CStr([Reports]![rptMyReport]![Expr3])

If Len(strExpr3) = 3 Then
   strExpr3 = strExpr3 & "00000"
Else
   strExpr3 = "470502" & strExpr3
End If

GetID = CInt(strExpr3)

End Function
'=====

HTH

mmcdonal

Ooops, I would change the Function statement to this as well:

Public Function GetID()



mmcdonal

thank you very much, your response helped me. my problem was with string being concatenated to integers and integers having values that exceed the percision value. but it's all solved now.
i have another question. i'm trying to export the result of a query that i have created into a text file.
my guess is that i need to use vba. so my suggeseted function is:
Public Function ReadFile()
Open "C:\somewhere" output file #1
write #1 feild1,feild2,feild3.....
close #1
End Function

now what this function is missing is the query name or anything that tells the function which query to talk to.
how can i tell my function that this is the query that you need to take the data from?
and then when this is all done, how do i tell my query that you need to use this function?
what this function is supposed to do is read the data from the query and write it to the text file with no neat format at all, something that an old mainframe system can understand.
i hope that what i said makes sense, and sorry if i sound like a total beginner but the truth is i'm really new to the access/vba stuff.
thank you

I can help you with this, but I have to go work in another building for a few hours.

Do you want to create a new text file each time, or overwrite an existing file, or append data to an existing text file?

Do you need column headings in the text file? How do you want the fields delimited? (comma, tab, etc)

You will need to access the file properly, or check for its existence, then create a recordset from the query, then write lines to the text file. I have done a lot of this. It is part VBA, part VBScript. You can also have Access just export the query to file (open the query, File>Export, as csv file, or using DoCmd), but writing your own code gives you more control.

Also, you will need to determine what you want to trigger this event. Do you want to push a button and have this happen, or do you want it to happen automatically when you open a form or report or close a form or open or close the database etc?



mmcdonal

thank you again. To answer your questions, i want to create a new file everytime, i don't want to column headings and i want my feilds to be delimited by tab. the file i expect would look something like this:
0612901305616 20050216Graphics Services: Outsourcing
and no i can't use file>export because it shows the column headings. and it adds quots to the feilds! this is what i got when i tried it.
    "Expr1" "Expr2" "FSID"
    "111" "20050407" "470502111"
I've done some searching and i was able to write this function that worked but didn't give the desirable reults.
this is my code:
Public Function ReadFile()
  Dim db As Database
  Dim rs As Recordset
  Set db = CurrentDb() ' Open pointer to current database
  Set rs = db.OpenRecordset("Query2") ' Open recordset on saved query
  Open "mypath" For Output As #1
  Do While Not rs.EOF
  Write #1,rs![Expr3];
    'Write #1, rs![FAMISID];
    rs.MoveNext
  Loop
  rs.Close
  db.Close
  Close #1
End Function
the result was this: "111","470502111",
and when i removed the semicolon it was like this
"111"
"470502111"
i want the results without the commas and the quotes(" "). i've read in help that i can use (print) instead of (write) to add tabs instead of commas but they said that write is a better choice if i want to use that file for input, which is true in my case because i want to use it for input to the mainframe system. tell me what you think of the code and if u have a better idea to get my text file without commas or quotations.
what do i want to trigger this event? probably it would be best if it happens automatically when i open or run my query.
thank you for helping me.