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 m using autonumber in my ID field.My other table fields are mandatory. There is no problem when I enetered Record completely. Now if I have 20 records. automuber is also 20. Now I want to eneter new record. 21 autonumber generated. But I have not completed the form/table.or I didnt want now that record.So I didnt save it.
But next time I come autonumber 22 is generated for my new record. Now I have 1,2,3,......19,20,22,23....
here 21 has lost.Now is there any option to recover or restict that without completing the full table if the record had not saved autonumber again generated.
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....
I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
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 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
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.
Can someone put a newby straight please? Why do I get a 'NAME' error from this? Many thanks in advance Don
Select Case SortOrd Case "a": Me.SortOrd.DefaultValue = "B" Case "b": Me.SortOrd.DefaultValue = "C" Case "c": Me.SortOrd.DefaultValue = "D" Case "d": Me.SortOrd.DefaultValue = "E" Case "e": Me.SortOrd.DefaultValue = "F" Case "f": Me.SortOrd.DefaultValue = "G" Case "g": Me.SortOrd.DefaultValue = "H" Case "A": Me.SortOrd.DefaultValue = "B" Case "B": Me.SortOrd.DefaultValue = "C" Case "C": Me.SortOrd.DefaultValue = "D" Case "D": Me.SortOrd.DefaultValue = "E" Case "E": Me.SortOrd.DefaultValue = "F" Case "F": Me.SortOrd.DefaultValue = "G" Case "G": Me.SortOrd.DefaultValue = "H" 'Case Else 'Me.SortOrd.DefaultValue = "Oops" End Select
I have a drop down list box on my form called FieldA. I would like for FieldB to update with a number depending on what is in FieldA. For example, if FieldA has Red, I would like FieldB to return a 2. I know this needs to have a case function done, but how do I get it linked to FieldB?
Hello Everyone, Why does my database grow so large during the development stage? After compacting the database it is 64 megs, but only after a small number of changes it grows to 500 plus and more if I don’t re-compact it.
Hi everyone, please help, i am in depserate need here. Am i right in thinking nested IF functions are limited to 7 variables, i need to choose from 12, so i need to use something different
it seems like a CASE function could do what i want it to, but i really have no clue where to start, shall i enter this into a query criteria box, or on the form itself ina new field box...??
Can you give a few hints as to what the code would look like, i have two fields, obviously. One is 'lesson_type' and one is 'cost' i want the cost to change depending on what is selected in the lesson type box.
This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.
But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case
Is there a way, either within Access or not, to change the city names in that table to Proper Case?
