Solved

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

Posted on 2013-01-15
9
338 Views
Last Modified: 2013-01-15
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
Comment
Question by:jtflex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38780582
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38780587
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
 

Author Comment

by:jtflex
ID: 38780907
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 61

Expert Comment

by:mbizup
ID: 38780914
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
 

Author Comment

by:jtflex
ID: 38780982
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38781005
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38781017
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
 

Author Closing Comment

by:jtflex
ID: 38781025
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38781027
Glad to help out!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question