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 RepliesHello,
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.
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
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?
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
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
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
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?
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
In a query I have placed functions that work fine. Now I needed to round a currency number to the nearest $10. ex. 224.49 would be 220.00. I used round(xxxxxxx,-2). this gives me an error. positive 2 works fine. What's the deal? thanks for anyone who can help me. :)
View 3 Replies View Relatedhi
i have a DB which tracks order numbers,
the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail
i want to open a form and only show records from trackermaindata that contain my useremail -
SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData, userDetails
WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;
when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail
i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.
can anyone give any reason why this should not work? it seems straight forward enough!!
thanks for any help
Hi
I have set up a form to run a query, setting the following VBA to pick up values from the combo boxes in the form. The only problem is that I cant seem to get this working. When i press the command button it won't even get past the Dim db As Database line.
Private Sub Command10_Click()
On Error GoTo Errorhandler
Dim db As Database
Dim qd As QueryDef
Dim vWhere As Variant
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete "qry_email2"
On Error GoTo 0
vWhere = Null
vWhere = vWhere & " AND [Status]=" + Me.cbostatustype
vWhere = vWhere & " AND [Substatus]=" + Me.cboSubstatus
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub1
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub2
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub3
If Nz(vWhere, "") = "" Then
MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
"Search Cancelled.", vbInformation, "Search Canceled."
Else
Set qd = db.CreateQueryDef("qry_email2", "SELECT * FROM tblgeneralcontactdetails WHERE " & _
Mid(vWhere, 6))
db.Close
Set db = Nothing
DoCmd.OpenQuery "qry_email2", acViewNormal, acReadOnly
Me.Command10.Requery
End Sub
help! I am now proper stuck with this.
Thanks in advance
Shapman
Im trying to add fields together in a query (these fields are results churned out by an iif expression), however access just concatenates the fields instead of summing. Access will not allow me to change the format of the original fields either.
Any ideas?
I have a query that created using the query builder.
THis is the SQL
But this wont work when using it in the VBA Code Line
I know this is a syntax issue...
Any thoughts?
Code:UPDATE dbo_Core2 SET dbo_Core2.STORAGE_LO = "DESTROYED", dbo_Core2.[DATE] = forms!frm_Main!Combo0, dbo_Core2.DISPO = forms!frm_Main!Text3WHERE (((dbo_Core2.STORAGE_LO)=[forms]![frm_Main]![Combo7]));
I'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had
[Forms]![MAIN]![Text440]
refering to the unbound text box on the form already mentioned.
This worked fine, but the following:
Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as:
Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
I am using Access2000...
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work.
Why?
I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked.
Any ideas?
I have a Query pulling data from several tables. If there are empty cell with no data in them, they cause the Query to completely skip that record. It does not show up in the Query. Not practical since most of the data int the table will not be entered until some time later.
What gives? Is there a way around this?
Thanks.
Hi,
I am trying to get DATEADD to work in a query where the parts are called by different colums on the query like so:
Code:ExpiryDate: DateAdd("" & [DDType] & "",[DDNum],[Date])
At the moment the above will return the [DATE] without adding on the numbers
The [DDNum] is the number and works fine
The [Date] is the date and works fine
I know this becase the following code will give a new date
Code:ExpiryDate: DateAdd("d",[DDNum],[Date])
Could someone kindly let me know what I am missing in my syntax to get this working - I can get it working in ASP but I also need it to work in an Access Database
thank you for your time
I want to use Recordset to work with query but get the error "Run-time error '3061' too less parameters. Expected one".
Code:
Dim rst As Recordset
Dim strSQL As String
Dim krojenie As String
strSQL = "SELECT * FROM powtorzeniaDoKrojenia"
[Code] .....
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies View RelatedGoal: : To improve an application's performance by basing sub forms and combo
The application has a front and back-end. There is no SQL server involved. I use a module for, seeking out the back-end and automatically linking to the back-end in order that I might be able to provide updates. This has worked for me since first setting everything up.
First let me define what I mean by, “sub form” as to how I am designing my application. I have a number of, what I call, “main (unbound) forms” that are used to open up separate application modules. Each module contains a number of hyperlinks to each of the other application modules. Also, each main form (application module) contains a tab form with bound forms. Therefore, I am calling these lower level bound forms, sub forms and basing them on query files instead of tables. Many of the tables have fields for collecting information across a second form. I did not want to bring back all the fields when they are used in any of the two are three forms bound with the same table.
Problem: When I base either a main form or a sub-form on a query, that particular front-end query form no longer links to the back-end database table via a query file whenever I import everything g into a new/fresh version of the front-end application. Whnever I open one of the query files via the form, I get an error message in each of the fields indicating it can't fine the field even though the table is listed with all the fields. No, I do not have the same problem with forms based on tables.
I suspect that my problem is in what I am calling a main and sub form; and that the main form is really the first level bound form and never the top unbound form if one is used. I have a lot of forms. I just wanted to get some feedback on
my suspicions or find out what else could be the source of my problem before re-doing everything.
Thanks,
John
The user currently manual checks the Use Part? box but would I would like to automate with an update query. I have this query that needs to be updatable but has a group by and min to get the correct results. I plan to run this query by another update query to update the table, but its currently not updatable. Is there a workaround or does anyone have another scheme to go at it. Here is my current queries.
SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?]
HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0))
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];
I have another query that I used a SUB SELECT ON THE [Alt Unit 1] but does not group the other fields to get the results but it is updatable.
SELECT [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND (([t_BOM Part Cross].[Alt Unit 1])>(SELECT MIN([Alt Unit 1])>0
FROM [t_BOM Part Cross])))
ORDER BY [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #];
thanks for any help available
I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
So any idea to eliminate this??
Hi
I'm trying to finish a query that calculates the time worked in the AM and PM, adds them together, then takes that away from the standard working day.
I've done everything else, but when I take the hours worked away from the standard day it always ends up with a positive number, regardless if they haven't worked enough hours.
I've looked through the forums and everyone is talking modules, but thats over my head.
Thanks for looking, apprieciate the help.
Alan
With the imense help of some of the people in this forum, I have scraped together a nice little database.
There is one function, however, which is not working.
I have a query that should run from a form I have created. The form has multiple criteria and all seem to be working except for the date fields... "First Order" and "Last Order".
The way I have it set up is that I have a table of dates. The form has a list box of the dates (first of the month, for 4 years).
The source data in the form reads: SELECT [TDateSelect].[StartDates] FROM TDateSelect ORDER BY [TDateSelect].[StartDates]; (for each of the 2 "minimum" dates)
and SELECT [TDateSelect].[EndDates] FROM TDateSelect ORDER BY [TDateSelect].[EndDates]; (for each of the 2 "max" dates)
The user is allowed to select a date range for the first order and the last order. (4 dates altogether).
The form looks fine.
In the query, I have set the criteria for the last date as:
Between [Forms]![FSetOtherCriteria].[MinLast] And [Forms]![FSetOtherCriteria].[MaxLast]
When I run the query (even with a wide date range) I get no results. When I remove the dates from the query, or enter the limits manually, the query works like a dream.
Any ideas???
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA