Auto Inserting in a field and Upper(text) Problems 2

[RobotMush]

I have two fields that I am working with.

One field has the same data entered every time. Is there a way to make it record and save the same data automatically?

The other field take the last four letters from another field "ExtA:Right(Link,4)", drops the last letter
"ExtB:Left([ExtA],3)"and is supposed to put the result in upper case "FileType:UPPER([Extb])" However, when I try to close the query that I am working with I get the following error. "Undifined Function 'Upper' in Expression" Am I using the wrong KeyWord for what I want to do?

Thank you for your help

RobotMush (Technical User) Self Taught

 

[Remou]

Have you looked at Default Value? I think you want UCase.

 

[RobotMush]

Thank you Remou, I know that I have done it before. Deleted the query in working on getting everything to run so I didn't have anything to fall back on to see how I did it.

It works fine now. Do you have any suggestions on how to get a field to automatically insert the same data in every record for that field? I.E. I am needing the Field to show "J:/C/" in that field for every record.

Thanks for your help.

RobotMush (Technical Users)Self Taught

 

[Remou]

I think you need Default Value. If you set a Default value for the control or field, that value will show up for every new record. If you want the value to show up for records that are not new, you need to add a little code to check if the control is blank and if it is, set it to the value you want. The On Current event would be good for such a check.

 

[RobotMush]

Thank you again Remou. Will this place the value into the table? or just in the quieries? I am finding that both my fields are working great now and producing the type of data I am looking for. However, they are not showing up in the tables.

You help is greatly appreciated, you get another star

RobotMush (Technical User) Self Taught

 

[lars7]

You could put the values in the "default value" in the table.

 

[Remou]

Ok. I was not paying enough attention. You may not need a query, if you want to post the SQL, I might be able to tell. Let us say that you do. First, make the query fairly straighforward, that is, forget about the UCase stuff, it will not be returned to your table. Next, check that you can enter data into the query. Now build a form based on this query.
For the uppercase field, you can set the Input Mask if you know how long the field is going to be. This:
>CCCCCCCCC
Would cause all the characters after > to be uppercase. Check Help for more on Input Mask. Alternatively, you can use the After Update event for the field to convert:

Private Sub [i]txtTextBox[/i]_AfterUpdate()
Me.[i]txtTextBox[/i] = UCase(Me.[i]txtTextBox[/i])
End Sub

For the field that you want to repeat, you need to set the Default Value for the control to "J:/C/".

 

[RobotMush]

Thank you lars7, I am able to do that with the single item entry, however, the other data that needs to be inserted into the other field is a bit more difficult to do. I am extracting the last four characters in a long string "pdf#" from one field in my query then taking that data and extracting the first three characters "pdf" and using UCase on it "PDF" to be placed in the final field that is part of the table. I tried using the following in my default value for the final field UCase(Left(Right([fieldname],4),3)) and it is not accepted. Any suggestions?

Thank you for your help in this matter

RobotMush (Technical User) Self Taught

 

[RobotMush]

Thank you for taking the time to help this mush brained techi get his database working properly. I will need to print out what you have told me and reread it as I go through the suggestions. I believe I am seeing what you are telling me. The field will not have more than 3 characters so a >CCC should work, however as I have told lars7 I am needing the first 3 of the last four characters in a long string that will give me the file extension.

Will let you know how it goes.

RobotMush (Technical User) Self Taught

 

[Remou]

Try:
[tt]Mid(txtTextbox, Len(txtTextbox)-3,3)[/tt]

 

[RobotMush]

Thank you, will try that. Would that be in the Default Value of the Table Field I am wanting to have the Data in?

 

[Remou]

I am afraid I am getting very confused. Perhaps if you posted a little sample data, both what you have and what you want?
The line above should get you the first three of the last four characters, it will not work as a default value, but could be used to update a control or as the Control Source for a control.

 

[RobotMush]

Here is the sql view of the query I am working with.

SELECT
tblHyperlink.tblHyperlink_ID,
tblHyperlink.HypLnk,
tblHyperlink.Year,
tblHyperlink.SubDir,
tblHyperlink.FileData,
tblHyperlink.Local,
UCase([Ext2]) AS FileType,
Right([HypLnk],4) AS Ext1,
Left([Ext1],3) AS Ext2
FROM tblHyperlink
WHERE (((tblHyperlink.HypLnk)>" "))
ORDER BY tblHyperlink.HypLnk;

The "tblHyperlink.FileData" I have working properly I think, it is not putting the data in the current records but I hope it will as I input the data in.
From UCase~ to ~As Ext2 are what I am using to generate the extension for the FileType with has "tblHyperlink.FileType" as the same field name. I am pulling the data from "tblHyperlink.HypLnk" that has data like the following example.

#\\MainDrive\SubDirectory\FolderName\Client Name\122004\Data\ClientName 122004 Data FileData.pdf#

As it is a hyperlinked field it has the # in before and after the detail making it a little harder to extract the file extension which in this case is "PDF"

Hope this makes it a bit clearer

Robot Mush (Technical User) Self Taught

 

[lars7]

Thanks RobotMush,
My first star, I will never forget you.:-X

 

[RobotMush]

Your welcome lars7, first of many I am sure

RobotMush (Technical User) Self Taught

 

[Remou]

A few thoughts.

This:
[tt]SELECT tblHyperlink.tblHyperlink_ID, tblHyperlink.HypLnk, tblHyperlink.Year, tblHyperlink.SubDir, tblHyperlink.FileData, tblHyperlink.Local, UCase(Mid([hyplnk],Len([hyplnk])-3,3)) AS FileType
FROM tblHyperlink
WHERE (((tblHyperlink.HypLnk)>" "))
ORDER BY tblHyperlink.HypLnk;[/tt]
Should get you your extension in a few less steps.

I have never liked hyperlink fields, they are a terrible nuisance to edit. If you use an ordinary text field, you can use FollowHyperlink in a click, or better still, double-click event, to open the relevant document. It will make editing and validation so much easier.

You do not say whether you are using a form. Forms make life a lot easier. The stuff you are doing in the query could be done more safely in a form. That is where the muttering about Input Masks and Default Values came from. You could base your form on a simple query that shows the fields you want from the table and process the data in a variety of suitable events.

I am still somewhat confused, what problems are you having with your data? Or is everything working ok at the moment?

 

[RobotMush]

At present everything is working ok, I do have a form setup from this query. Basically I am using what little knowledge I have to do what I want and I will try your suggestion to have my Form do the work instead of the query.
Didn't know about the follow hyperlink which would have saved me a lot of trouble due to the higher tech lords moving the links about and forcing me to redo about 4,000+ records (Not a pretty sight let me tell you) Will study up in my "Access 2000 Bible" and "Beginning Access 2000 VBA" They are both a great help to me, when I know what I am looking for that is.

Now I'm off...er that is I'm going to work some more on the Database, thank you so much for your help

RobotMush (Technical User) Self Taught

 

[PHV]

For better understanding on Hyperlinks, have a look at the VBA help about the HyperlinkPart method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

Thank you PHV, Will check it out

RobotMush (Technical User) Self Taught