Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Access 2003 Change (Edit?) Field Format on a Form from a Query

I am progressing with an application that returns data from a query and displays it on a form.

The data in one of the fields (called Report_Number) is displayed in the following format:

121234567
120123456
120012345
120001234
120000123
120000012
120000001

I have a function that takes that data and changes it to the following:

12-1234567
12-123456
12-12345
12-1234
12-123
12-12
12-1

Bascially separating the 1st two numbers, followed by a "-" and then stripping out the rest of the leading zeros.

The query I built returns the data to a Form displayed as a datasheet.

I am trying to apply this function to it's field in the form but I don't see what Event choice is the correct one to use.

The closest I got it working is if I apply this function to an unbound text field, the 1st record has this  field formated correctly, but the rest of the records have their own fields displaying the 1st records data.

97-123456 (the correct one)
97-123456
97-123456

etc.

Where do I apply this function to change the data?
0
jtflex
Asked:
jtflex
  • 6
  • 3
1 Solution
 
mbizupCommented:
In a continuous form, to get distinct values you need to apply your formula as a new field in your query and use that field as the control source for your textbox.
0
 
mbizupCommented:
Something like this:

Modify your form's recordsource query to include your function

SELECT FieldA, YourFunction(FieldA) as FormattedFieldA, FieldB, etc
FROM YourTable

Open in new window



and then set the control source of your text box to

= FormattedFieldA

Open in new window

0
 
jtflexAuthor Commented:
Hi mbizup,

Keep in mind I'm very new to building stuff in Access and I'm kinda' learning as I build this.

I added the following statement to a test query called "Case Conversion" that I built in the RecordSource  of the form:

SELECT TIBURON_INMAST_VIEW.Report_No, TIBURON_INMAST_VIEW.Reported_Date, TIBURON_INMAST_VIEW.Reported_Time, ConvertCaseNum(TIBURON_INMAST_VIEW.Report_No) AS APDrpt, TIBURON_INMAST_VIEW.Reported_DOW
FROM TIBURON_INMAST_VIEW;

The function I built is called ConvertCaseNum and it exists in the form itself.

When I run the query it says "undefine function 'ConvertCaseNum' in expression".

Does that function have to exist in the query?  If so how do I put it there?  I don't where you can add vba code to a query?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
mbizupCommented:
Place your ConvertCaseNum into a seperate code module, and declare it as:

Public Function ConvertCaseNum( etc...

When you save the module, give the module a distinct name such as modGeneralFunctions.

Doing this should make your function globally visible throughout your database.
0
 
jtflexAuthor Commented:
That helped (and now I know have to add user functions, thanks).  

However, now the return I get states "The expression you entered has a function containing the wrong number of arguments".

I created a simple function as a test listed below:

  Public Function TestThis() As String
Dim strTestFunc As String

    strTestFunc = "Test This!!!!"

End Function

Open in new window


I used that "TestThis(TIBURON_INMAST_VIEW.Report_No) and got the same error.

Also here is the original code of the function, maybe I not declaring it right?

  Public Function ConvertCaseNum() As String

Dim strCaseYear As String
Dim strCaseNumber As String
Dim arrCaseNumber() As Byte
Dim intPointer As Integer
strCaseYear = Left(TIBURON_INMAST_VIEW.Report_No, 2)
strCaseNumber = Mid(TIBURON_INMAST_VIEW.Report_No, 3)
arrCaseNumber() = strCaseNumber
intPointer = 1

Do While arrCaseNumber(intPointer) = 0 And intPointer <= Len(strCaseNumber)
    intPointer = intPointer + 1
Loop
strCaseNumber = Right(strCaseNumber, Len(strCaseNumber) - (intPointer - 1))
ConvertCaseNum = strCaseYear & "-" & strCaseNumber

End Function

Open in new window


Thanks for looking at this for me.

Joe
0
 
mbizupCommented:
your function calling statement passes your field to the function (I've bolded the field that is getting passed):

 ConvertCaseNum(TIBURON_INMAST_VIEW.Report_No) AS APDrpt


Your function declaration needs to provide a variable with data type, local to that function to accept your field name:


 Public Function ConvertCaseNum(strReportNumber AS String) As String

Open in new window


and then your function needs to use that local variable name, not your actual field name:

Public Function ConvertCaseNum(strReportNumber AS String) As String

Dim strCaseYear As String
Dim strCaseNumber As String
Dim arrCaseNumber() As Byte
Dim intPointer As Integer
strCaseYear = Left(strReportNumber , 2)
strCaseNumber = Mid(strReportNumber , 3)
arrCaseNumber() = strCaseNumber
intPointer = 1

Do While arrCaseNumber(intPointer) = 0 And intPointer <= Len(strCaseNumber)
    intPointer = intPointer + 1
Loop
strCaseNumber = Right(strCaseNumber, Len(strCaseNumber) - (intPointer - 1))
ConvertCaseNum = strCaseYear & "-" & strCaseNumber

End Function

Open in new window

0
 
mbizupCommented:
Quick comment about the test function and why it was not working...

Your function call is passing a parameter to the function:
          TestThis(TIBURON_INMAST_VIEW.Report_No)

But your function declaration has no defined parameters:

                       Public Function TestThis() As String


Revising your function like this would work:

  Public Function TestThis(strTest as string) As String
           Dim strTestFunc As String

          strTestFunc = "Test This!!!! ----- The value passed to the function is:   " & strTest

End Function

Open in new window

0
 
jtflexAuthor Commented:
Excellent, that work perfectly and I learned a lot more.

The only issue now is I see my function didn't calculate all of the data correctly but I will try to troubleshoot that on my own for now.

Thank you very much mbizup
0
 
mbizupCommented:
Glad to help out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now