Solved

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

Posted on 2013-01-15
9
303 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now