VBA code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I don't think you can use the Len function with a numeric argument. Also,
you can't concatenate strings onto Integer values. Plus, if you have an
Integer value, you won't be able to add 5 zeros to the end - that would
exceed the precision of an Integer value. You would need a Long for numbers
that large. And if it were me, I wouldn't directly alter the value of my
parameter within the function.

I would use something like:

Public Function GetID(Expr3 As Long) As Long
GetID = Expr3
If (Expr3 >= 100) And (Expr3 >= 999) then
GetID = Expr3 * 100000
Else
If (Expr3 >= 10000) And (Expr3 <= 99999) Then
GetID = 47050200000 + Expr3
End If
End If
End Function

HTH,

Carl Rapson
 
If you want to put your code in VBA you would click on the bar of the
section of the field you are trying to change (ex.. Detail) and click on
properties then the event tab, depending on when you want this event to
occur (ex.. On Print) you will choose [Event Procedure] in the drop down
arrow box, and the VBA screen will come up, type your code here.

If this is way off I apologize in advance.
KB
 
thank you , 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


Carl Rapson said:
I don't think you can use the Len function with a numeric argument. Also,
you can't concatenate strings onto Integer values. Plus, if you have an
Integer value, you won't be able to add 5 zeros to the end - that would
exceed the precision of an Integer value. You would need a Long for numbers
that large. And if it were me, I wouldn't directly alter the value of my
parameter within the function.

I would use something like:

Public Function GetID(Expr3 As Long) As Long
GetID = Expr3
If (Expr3 >= 100) And (Expr3 >= 999) then
GetID = Expr3 * 100000
Else
If (Expr3 >= 10000) And (Expr3 <= 99999) Then
GetID = 47050200000 + Expr3
End If
End If
End Function

HTH,

Carl Rapson

desprate said:
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
Click to expand...
Click to expand...
 
i found a way to do but it gives the feild sin quotations and separated by
comas.
here's my code any suggestions?
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![FISID];
rs.MoveNext
Loop
rs.Close
db.Close
Close #1
End Function
i want the resulting file to be wihtout headings, comas or quotations marks
thank you
desprate said:
thank you , 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


Carl Rapson said:
I don't think you can use the Len function with a numeric argument. Also,
you can't concatenate strings onto Integer values. Plus, if you have an
Integer value, you won't be able to add 5 zeros to the end - that would
exceed the precision of an Integer value. You would need a Long for numbers
that large. And if it were me, I wouldn't directly alter the value of my
parameter within the function.

I would use something like:

Public Function GetID(Expr3 As Long) As Long
GetID = Expr3
If (Expr3 >= 100) And (Expr3 >= 999) then
GetID = Expr3 * 100000
Else
If (Expr3 >= 10000) And (Expr3 <= 99999) Then
GetID = 47050200000 + Expr3
End If
End If
End Function

HTH,

Carl Rapson

desprate said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top