I'm using the standard duplicates query on a field called trackTitle:
SELECT TrackTitles.trackTitle, TrackTitles.akTrack
FROM TrackTitles
WHERE (((TrackTitles.trackTitle) In (SELECT [trackTitle] FROM [TrackTitles] As Tmp GROUP BY [trackTitle] HAVING Count(*)>1 )))
ORDER BY TrackTitles.trackTitle;
I need to be flagged if there's a title in a different case - for example, the track "NEXT Year" might have versions appear as "Next Year" or "next year". These versions would need to be flagged and then corrected by hand.
Eyeballing a list of 4,000 new tracks isn't cutting it.
If I could get the duplication to be case sensitive, I could then use a left([trackTitle], 5) to weed down the review list - much easier to pick out the thorns.
Any ideas on how I could make that duplicates query case sensitive?
I know when creating a text field in the format option you can use the > or < sign so that when text is entered it automatically changes it to uppercase or lowercase - but i need it to be Title Case, any one know how I can do this....
table name : Schedule Field 1 = Vessel code Field 2 = Voyage Field 3 = ETA Field 4 = berthed Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
Hi All, I want to change existing entries in a table to upper case format. I know that it is possible with a query (update I suppose) using an expression with the "UPPER" function. But I am not quite sure how to implement it in Access. A little help would be welcome...
Is there a place within an Access database besides a table where you can store a flag (text or boolean or number) that persists after the database is closed and can be checked when the database is opened (using VBA)?
How can I change the default message (as you can see in the picture) that appear every time that I try to update my record?
I want to write something like "You Have to select a different Number"
And one more question, the message only appear when I'm pressing the record selectorbutton only after the user has already filled all the form) is there a possibility that I can write a code that will check for duplicate data immediately after the user typed it?
How to control the below message to something like this through VBA:
"The Job: " & JobNumber & " already exists in the table." or to open an alternate form where they can make the change. Allowing duplicates is not appropriate for what I am trying to accomplish. I just want a friendly way of rerouting a user to understandable answer.
instead of this:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT COLUMN in UPDATED HEADCOUNT - EMPID FORM CELL user will input an EMPID - newEMPID FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
I have an Option Box that contains three Report Choices, Report1, Report2 and Report3. I want to run the Reports. I coded using If-Then and it worked out fine. See code below:
Private Sub Frame0_Click() If Me.Frame0 = 1 Then DoCmd.OpenReport "Report1", acViewPreview ElseIf Me.Frame0 = 2 Then DoCmd.OpenReport "Report2", acViewPreview Else Me.Frame0 = 3 DoCmd.OpenReport "Report3", acViewPreview End If End Sub
I would like to get the same result using "Case." However, when I use the Case format, it does not show all the reports as it does in the IF -Then format. See Case format below:
Private Sub Frame1_Click() Dim Test As Integer
Select Case Test
Case Me.Frame1 = 1 DoCmd.OpenReport "Report1", acViewPreview
Case Me.Frame1 = 2 DoCmd.OpenReport "Report2", acViewPreview
Case Me.Frame1 = 3 DoCmd.OpenReport "Report3", acViewPreview End Select End Sub
Something simple is missing at the top. Please advise as to how to correct this?
Someone was helping me out and sent me the attached code to connect to a SQL server from Access2000 in a query. Access doesn't use the CASE statement, but the IIF statement instead. Can any of you help in deciphering the below CASE statement into an IIF statement?....
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?
Dim dbs As DAO.Database Dim rst AS DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Select * From YourTable") While Not rst.EOF And Not rst.BOF Select Case rst![System Number] Case 42144 ' do this Case 88754 ' do this Case Else ' do this End Select rst.MoveNext Wend rst.Close Set rst = Nothing Set dbs = Nothing
How can I set up a table so that as we assign a case it generates a new number with the year in front of it. I would like for the case number to be generated by the program and when someone enter a new case the program generates the next case number and assignes it to that case. The format we use for our file numbers are year-XXXX (ex: 07-0001). With the new year I'm trying to set this up so that as we start the year the computer will generate the new number.
I am trying to run the following query and getting an error. It looks like its because of the Case Statement.
SELECT Role.RoleID, Objects.ObjectID, CASE MID(Objects.ObjectName, 4, 2) CASE 'SR' 0 CASE ELSE 2 END AS AccessType FROM Role, Objects WHERE (Role.RoleID > 2115)
MAX(IIf([Book]='C',[Score]," ")) AS CScore, Max(IIf([Book]='C',[PercentileScore]," ")) AS CPercent, Max(IIf([Book]='C',[PassFail]," ")) AS C, Max(IIf([Book]='D',[Score]," ")) AS DScore, Max(IIf([Book]='D',[PercentileScore]," ")) AS DPercent, Here is what I thought may work, but it seems like there should be an easier way Case when Max ([Book]) = 'C' then [Score] else null as CScore, Case when Max([Book]) = 'C' then [PercentileScore] else Null as CPercent, Case when Max ([Book]) = 'C' then [Passfail] else Null as C, Case when Max([Book]) = 'D' then [Score] else Null as Dscore, Case when Max ([book]) = 'D' then [PercentileScore] eslse Null as DPercent,
I have two tables that have fields set to a text so that the ClientID is their name.
When I query, my queries don't take into account the case. So "K Smith" is the same as "K SMITH" as "k sMIth"
I am trying to write an unmatched query between two tables based on this ClientID but it will turn up no unmatched because it is not taking into account the case.
Any suggestions on how to match the cases, othere then changing the table?
Hello, I'm attempting to use a case statement in order to select fields from a column that have names and addresses mixed. I'm wanting to select only the fields that contain names. For example some fields start with an address of "1998 Sky Rd" or PO BOX, or Suite at the beginning of the field. I would like to first select all fields with names in the field and then use an update statement to move/switch fields to another column. The only problem is that I can't seem to get this query to work. Please help!!!
Thank you!!!!!!!!!
SELECT address1, name3=address1 as expr1
Case [address1]
When mid([address1],1,1) Like [A-Z] then [address1] When [address] is null then "" When [address] = "" then "" Else 'Null" End,
i have a form, and i would like that on one of the fields, as soon as i type something in it when i leave the field, i want it to automatically convert it to title case. can this be done please?
I am trying to calculate a date in the future based on an entered date and the sample falling into one or more categories.
Fields: Start Date, manually entered Interval, combo box, choice of monthly or weekly Pull Interval, combo box, choice of Long term, Short Term, or Stressed Conditions, combo box, choice of 25°C, 40°C, or 60°C 1 Week, calculated field 3 Month, calculated field
What I would like to happen is that when the Interval box is the choice of monthly that the 3 Month field calculates the date 3 months from the start date. Also, if the Interval field is weekly the 3 Month field is to be left blank. I have managed the Dateadd function to calculate the 3 month date, but do not know how to incorporate the condition of the weekly statement to leave the 3 month field blank. Also where should I put this, right now I have this calculation in a subform. Would it be best to generate this in a query? and if so how do I set this up?
Also, I need to select that the Pull Interval is Long term that I can only choose the Conditions field, 25°C, Interval field as Monthly, and only calculate the 3 month time point. Same thing with the Short term and stressed Pull Intervals, I want when they are selected to only allow me to look at fields relative to those conditions.
Any help would be extremely appreciated. I have several books and they are not spelling out exactly what I need, and with my limited programming experience I am stuck.
Thanks ChrisB37 ChrisB37View Public ProfileSend a private message to ChrisB37Find all posts by ChrisB37Add ChrisB37 to Your Buddy List
I have a linked table that updates regularly. It stores all words in Upper Case. I would like to mail merge out of a query but I would like to format the words so that the first letter is Upper Case and the rest of the letters are Lower Case. I have tried formating the field in the query but this doen't work.
