UCase Function Problem

Nov 1, 2004

I am taking an online Access class.

I have an assigment to create an Update Query.

The Update Query is to prompt name entry in all lower case, then convert it to upper case.

The Update Query must do the formatting, no utilizing the Input Mask.

This is the code I thought would work.

Field: Firstname
Table: Good Data
Update: UCase([Firstname])
Criteria: [Enter first name in lower case]

The parameter box part of the Query runs fine. When I run the Query the parameter dialog appears instructing you to enter your firrst name in lower case. After I enter the name in lower case & click "OK" the reponse is "You are about to update 0 row(s).". There is nothing entered in the table when I check it.

Can anyone tell me what I need to change to make the Query update the table with the uppercase name?

Thanks,
Yaani-Mai

View Replies

UCase Function Not Recognised

Apr 14, 2006

In my Access 2000 database I have a form which displays patients' treatment details.
I have a text box in which the Forename and Surname are concatenated to display the full name and the Surname is included in a UCase function so that it is displayed in capitals:

=[tblPatient.Forename] & " " & UCase([tblPatient.Surname])

This works fine on my office computer (XP professional), but when I run it on my home machine (XP Home) in order to carry out further development, the UCase function is not recognised. I get the #Name? message in the text box and if I try altering the formula I get the message:

The function you entered can't be used in this expression
*You may have used a DoEvents, LBound, Ubound, Spc or Tab function in an expression
*You may have used an SQL aggregate function, such as Count, in a design grid or in a calculated control or field

As far as I know, the versions of Access are exactly the same on my home and office machines.
Is anyone able to shed any light on why this function works on one machine and not the other?

Thanks,

Jules

View 2 Replies View Related

Ucase Help

Mar 17, 2005

hi all
I have a form that has textboxes, a combox and a subform, when i complete a record, I will like everything to capitalized in the table but I can't seem to figure out how to do this on the subform fields.
I tried the ucase function for the after update but the subform doen't have an after update

View 3 Replies View Related

Ucase Question...

Jun 23, 2005

We have a situation where mail sent to Canada using the mail merge is being returned because it's not in all caps. I've used the following code in each fields after update:

Me.ActiveControl = UCase(Me.ActiveControl)

What I'd like to do is make it so only the Canadian addresses are in upper case. This would be determined by what is in the state field. AB, BC, etc. Any U.S. address would need to be in proper case where the first letter of each word is capitalized.

Can this be done?

View 14 Replies View Related

UCase Problem

Jul 12, 2005

Hi,

I'm using code which convertes names of costumer into upper case when user type the name into control. But there is a problem.

If letters č,�,� are pressed they are not converted to upper case.

Code:

Private Sub Customer_KeyPress(KeyAscii As Integer)
On Error Resume Next

KeyAscii = Asc(UCase(Chr(KeyAscii)))

End Sub

please help

View 3 Replies View Related

UCase Does Not Work In A Query

Oct 27, 2006

A few years ago, I wrote an application in Access 2000 that worked fine. Last year I upgraded to Access 2003. Everything works the same, but the UCase, Left and Right text functions give error messages when used in queries. Anyone have a way to fix this?::confused:

View 2 Replies View Related

Update Query To Ucase

Oct 13, 2006

Hello,

I have a table with data in Title Case (England) and would like to run an update query or a code to change all the records to UpperCase (ENGLAND). Can anyone help me? Thank you.

View 2 Replies View Related

Problem With Lcase And Ucase Data In Table

Apr 12, 2006

Hello friends,
I have data in my table field as "Orange" and some other entries in as all lower cases "orange", which really should be representing the same values.

Now when i use SQL in my modules to query and process this data, or create my report, it collects them as different values i.e. "Orange" is selected as one category and "orange" as another category, when really it should be getting them all (Orange, orange, ORAnge) as one category.

Perhaps this is a basic question, but everything i have tried doesn't seem to work. Can anyone tell me the best way to handle this?

View 6 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Forms :: SUM Function Produces Error From Calculated Function

Jan 30, 2014

I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/

The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.

Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |

So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total

=SUM([Qty Unit] * [Unit Price])

All fine and well..... However, the additional functionality kicks in.

Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.

Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.

Code:

Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "

[code]....

its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.

=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error

View 2 Replies View Related

Date Function/need Time Function

Jun 9, 2005

We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function

View 9 Replies View Related

Now Function To Convert To Date Function

May 25, 2006

Hi all,

I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.

Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.

I tried to go back and change the NOW to DATE in VB, but the code does not run.

How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?

Thanks in advance.

View 1 Replies View Related

Function

Aug 1, 2005

Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?

What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...

To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:

Number1: Cint("")

This will add a blank field that is formatted as a number field when I run the make table query.

So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.

Anyway...that's what I'm hoping for.... I hope someone can help!

Gary

View 10 Replies View Related

Function Isn't Available

Nov 3, 2005

I'm having a problem getting a form to open. I'm using access 97 on on XP machine and whan I open my database I get Function isn't available in expressions in query expression '[Case Types]![theName]&"-"&Format([SourceDocs]![CaseNumber]&"","00")&"-"&Format([SourceDocs]![CaseYear]&"","00")'.

This database works fine on a Windows 2000 machine running Access 97 but none of the XP machines will open it.

Any suggestions?

Thanks,
Rick

View 8 Replies View Related

CHR() Function Not Available

Nov 10, 2006

I have a database in which I am designing a report which includes a name/address block. I am surprised to find that the report does not recognise the Chr() function in the text box control source string "=[Organisation] & Chr(13) & Chr(10) & [Address]..."; designed to insert a CRLF between the contents of the named fields.

I know the syntax is correct because I've checked by using the expression builder to replicate the code. (Expression builder includes the Chr() function, as expected.)

The interesting thing is that the function is recognised in other databases used on the same machine, both those created remotely and locally.

Anyone know what could be going wrong?

Stuart

View 7 Replies View Related

Function Is Not Available

Sep 4, 2005

I am using a crosstab query to gather info from a table. I am using the Format [DateAppt], "mmm". The query ran successfully before but I now get the error "Function is not abailable in expression in query expression 'Format ([DateAppt], "mmm")'. This is used for column headings in a report that gives results by month.

I am using Microsft Office 2000 Premium with XP Professional operating system.

I used the original disk to repair Office thinking that the function was missing from the function library but to no avail.

Does anyone know how to restore this function?

Thank you,
Jack

View 1 Replies View Related

Help With IIF Function

Sep 29, 2005

Just wonder if anyone could give me some help on an IIF function used in my query:

I have a table which has numerous �W� codes, the charging for an order does not occur if it has a W12 or a W14 code and has no �[Authorising Name]� BUT does however charge if the weight of the order is over 100kg

IIf([WCode]="W12" Or [WCode]="W14",IIf([Authorising Name]="",0,[charge], IFF ([weight]>99,[charge],0)))

I can get this working for a single W code but not for the 2 OR I can get it working for both Codes but then the weight side doesn�t.

I have tried all combinations of <> = < etc etc but still no luck., I think I need another OR but no matter where I put it I hit problems either with the function or the result.

Another small issue, my [authorising Name] field can appear to be empty, but in the field are spaces what is the best way for ensuring I capture both empty and theses space records ?

Thanks in advance

View 4 Replies View Related

More Function Joy

Oct 12, 2005

Got on more question (I hope) about functions.

I have a table that contains a year (school year) and an End Status Code, I need to flag students that have the end status code of 45 in school year 2005.

When I use the below code the column popluates with "#ERROR".

IIf([ST010]![SCH_YR]="2005" And [ST010]![END_STAT]="45","Y","N")

Any Ideas?

Again thanks for the help..

--pete

View 1 Replies View Related

How To Fix Mid Function

Oct 21, 2005

I have a question:

SELECT DateSerial(2000+mid([enter],3,2),Mid([enter],1,2),1) AS datevalue;

it works in 2000 windows,
but it doesn't works in XP windows.

Why?
The error message the function mid has a problem.

How can I fix the problem in XP widows.

Thanks.

View 4 Replies View Related

How To Use The Function Now

Dec 11, 2005

hi there
am new to access and i want to know how to use the function now in a query
like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/:) :) :)

View 2 Replies View Related

How To Use The Function Now

Dec 11, 2005

View 9 Replies View Related

IF Function

Dec 21, 2005

Hi

i need help using the "IF" function i think.

Basically i have 2 "columns"

A B
aaa
aaa
aaa aaa

Basically if i have say Column A that has some fields filled in and Column B with other fields filled in. I want to Populate a column C with either the stuff in A or B.

So If Column A is blank and Column B is blank, then i want Column C to stay blank.

If Column A is populated and Column B is blank, then i want Column C to be populated with what column A has.

If Column A is blank and B is populated, then i want Column C to be populated with what Column B has.

Thanks in Advance

D

View 14 Replies View Related

LN() Function

Jan 29, 2006

Can some please tell me if there is an equivalent of the excel LN() function in access.

I have tried to find it all over the web and beyond and cannot
:(
I dont mind if there is a long winded work around for this function.

I am using it in a query and it shows unrecognised function
but for some reason shows in the access help as below :confused:

LN
Returns the natural logarithm of a number.

Syntax

LN(number)

Number is the positive real number whose natural logarithm you want.

Thanks for your help
Cress

View 1 Replies View Related

Using Max Function In SQL

May 4, 2006

I have parts table in Access where a part id is automatically assigned to each part number. I am trying to get the last part number entered in the parts table by querying on the max part id. The max part id will always be the last part number entered in the table. I don�t want to display the part id though; I only want to display the part number associated with that max part id. This is what I have been playing with.

"select part_no, (max)part_id as part from tblPartsSignOut "

Can someone give me some insight on how to structure this query the correct way?
Thanks

View 1 Replies View Related

Max Function

Mar 12, 2007

I know that the Max functions get the highest value but is there a way to get the second or thrid highest value??

View 4 Replies View Related

Log Function

Apr 18, 2007

Within a formula in querry beside other values, I need to have a logaritham of some records, but the Log function is giving me other (higher) results then for exp. in Excell or with calculator !?I hope someone can spent little time to explain it what I am doing wrong!Thanks in advance!!

View 2 Replies View Related