Default Value In A Field Based On A Query
May 31, 2006
Hi,
I have a query which returns 1 value (The description associated with the maximum ID in the table).
I have a Text box on a form and I would like the default value of the text box to be the the value from the above query.
I have tried to build an expression using the expression builder by selecting the query and the field that I am interested in, in that query but I can't seem to get it working.
Could anyone please advise how I can assign a result from a query to the default value of a field on a form.
Many Thanks
View Replies
ADVERTISEMENT
Feb 13, 2006
Hi Everyone,
I hope someone can help.
I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).
Currently in order to set the default value, I have used the following code for each default;
Private Sub Form_Load()
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""
End Sub
However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.
Private Sub Form_Load()
Dim Drivename As String
Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """Drivename"""
End Sub
This is definetly not working, can anybody help, I have a feeling it is syntax but not sure where? :confused:
Robert88
View 7 Replies
View Related
May 19, 2005
I am creating a database that should be fairly simple. The database is for a local racetrack and I'm stuck on how to entering race results. I have four tables: Driver, Rank, Home, Class. The form to enter in the results contains a main form with a subform. On the main form there are two combo boxes, one to select the Class of the race, and one to select the Heat of the race: i.e Finals, 1, 2, 3. The subform currently has a text box containing the rank of the driver for that race and the driver's name. I want the Rank to automatically start at one and increase by one each time a new driver is added. The rank field is part of the primary key but cannot be an autonumber field because there will be multiple ranks in the rank table. The rank table contains a primary key of: Class_ID, Rank, Heat. Therefore, each class/heat combo needs to start at 1 and increase. I created a query that finds the max rank value of the class/heat combo and I thought I could use that value +1 for the default value of the Rank field but I'm not having any luck. I have attached the database so that you can see what I all have as there isn't any sensitive information. If it would be easier doing it some other way than using the query that is fine to. I'm open to anything that works. I also want the Driver combo box in the Rank form to be limited to only contain drivers whose Class_ID matches that of the selected class in the main form. I have the row source set up sot that it works when I make my first selection when entering the form but not when I change the selected class and I do have an After_Update event set for requerying. It seems this should be so simple but I'm just missing something. Thanks in advance for your help!
View 1 Replies
View Related
Oct 8, 2007
Hello.
I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])
It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.
This is probably a totally incorrect way to get this done, but I don't know how else to try it.
Thanks!
Stacey
View 3 Replies
View Related
Mar 4, 2015
How to set a Default value "0" in Query Extended Price Calculated field ?
I have attached the screenshot with explanation, how to changes the formula.
I have used below following functions but there is no workout.
Extended Price: CCur([Qty]*[BPrice])
Extended Price: CCur(Nz([Qty]*[BPrice],0))
Extended Price: Nz([Qty]*[BPrice],0)
Extended Price: ([Qty]*[BPrice])
Extended Price: IIf(IsError(([Qty]*[BPrice])),0,([Qty]*[BPrice]))
View 4 Replies
View Related
Jun 28, 2013
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
View 4 Replies
View Related
Oct 8, 2014
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies
View Related
Apr 23, 2014
I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
View 3 Replies
View Related
Feb 6, 2006
All,
I have a Postcode field in my table and I want to be able to check the data to make sure that it is a correct UK postcode.
Is there away that I can find out the format of the data, to be able to run a query against it, or is there a better way of doing it?
I need to account for all types of UK postcodes (A1 2BC, A12 3BC, AB12 3CD, WC2A 3BC). There are also foreign postcodes in this field.
Help appreciated!
View 2 Replies
View Related
Jun 28, 2007
Hi,
I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.
I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).
I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c)
and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.
Can anyone advise?
Thanks,
Ryan
View 1 Replies
View Related
Jan 24, 2015
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
View 8 Replies
View Related
Sep 15, 2005
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
View 2 Replies
View Related
Jul 12, 2007
I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?
View 1 Replies
View Related
Apr 11, 2008
Hi Guys, I'm new here and fairly new to anything indepth with Access.
I'm using Access 2003 and have a table which has a number of customer records. The two relevant fields are the CustomerID which is a simple integer and OrderDate obviously this is a date field!
What I need (to link in with the other forms) is a query that will return one record for each CustomerID it should be the most recent OrderDate.
I've had a few attempts and done a couple of searches but can't seem to find exactly what I'm looking for. If someone could point me in the right direction I'm happy to play with sample code and read up on bits other people have done (I tend to learn better that way) rather than just being given some code!
Thanks for any help you could can provide.
View 1 Replies
View Related
Jul 31, 2013
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
View 3 Replies
View Related
Jan 24, 2015
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
View 2 Replies
View Related
Jan 20, 2008
Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.
Basically if the field HOTOtal shows any vaue between:
16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)
I thought I could do a case statement on report open but I am getting an error that my expression contains no value.
Here is the case statement
Private Sub Report_Open(Cancel As Integer)
Select Case Me.HOTotal
Case 16 To 30
Me.HOType.Value = "Definitely evening type"
Case 31 To 41
Me.HOType.Value = "Moderately evening type"
Case 42 To 58
Me.HOType.Value = "Neither type"
Case 59 To 69
Me.HOType.Value = "Moderately morning type"
Case Else
Me.HOType.Value = "Definitely morning type"
End Select
End Sub
What else can I do?
View 2 Replies
View Related
Nov 16, 2013
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
View 2 Replies
View Related
Sep 10, 2013
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
View 1 Replies
View Related
Jan 18, 2015
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
View 1 Replies
View Related
Oct 29, 2013
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
View 3 Replies
View Related
Oct 18, 2014
I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.
But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.
View 3 Replies
View Related
Sep 4, 2014
I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].
Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:
[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5
Customer#1 StockType#2 4
Customer#2 StockType#1 5
Customer#3 StockType#1 5
View 6 Replies
View Related
Jan 24, 2006
Hello. I am looking for a way to make a field value automatically default to
the value of the immediate prior field. For example, I am in record 11, and
in Field "City" I type "New York." When I create record 12, I want
City to automatically default to "New York." Is there a way to do this?
Thanks
View 2 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
Apr 25, 2005
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View 4 Replies
View Related
