i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.
in my Query1, i have 2 fields that use a question in the field part of the qry like:
Desired FiscalYear: [Enter Fiscal Year] EG 2007
Desired FiscalMonth: [Enter Fiscal Month] EG 2
And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.
I start it out here but it is wrong i know.
Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:
We still use Visual dBase 5.6 for most all of our database operations. We were considering the change to Access 2007. I was wondering about numeric fields. There are like four different options of numeric fields. Which would I choose for just a plain number field? Also how do I set a fixed field width?
Thanks for any help. I hope this is in the correct spot.
I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code: SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID WHERE (((tblMaterialSpecifications.ActiveInactive)=-1)) ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code: Option Compare Database Option Explicit Private Function IsDuplicateRecord() As Boolean On Error Resume Next Dim PreviousRecordID As Long IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
Have a Make table query that needs to create (add) several new fields where each field must be numeric design.
Have tried:
Score1: Not Null - does not seem to work (results in a Binary field) Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.
Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.
I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.
How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables. A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it. I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response Set dbs = CurrentDb Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I have a form that I use to enter information onto a table. Lately I have noticed that there are few duplicate names been entered into the db. The form has 2 txtboxes for the persons name. The table also has the 2 fields.
How can I make a Dlookup check both fields before allowing the new entry?
I'm trying to find a code that will allow me to check a value of a yes/no checkbox based on a username.
What I want to happpen is:
1. The code looks to see if the username that is entered in "txtUsername" field on the form matches the "empUsername" value on the table "tblUsers." 2. If there is a match, I want it to look at the value for the field "Admin" on "tblUsers" to see if the value is checked as true. 3. If the value is true, I want it to open a specific form "frmAdmin." If it is false, I want it to open a form "frmMain."
VBA codes not recognizing the Admin field and instead taking all users into the frmMain.
I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. The figures of my Access database are below.
I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure
Table1: ID (Autonumber) Number Code Grade
I have the following data: Number Code Grade 4 15 68 4 52 78 4 17 69 5 85 83 5 28 17
I am using this to retrive values for number 4 but I only get the first row DLookup("Code", "Table1", "[Number] = 4") DLookup("Grade", "Table1", "[Number] = 4")
I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?
I am trying to Dlookup two fields with dates in them. If the Dlookup returns that today is in the range (e.g. 06-03-2015 in the first field and 10-03-2015 in the second) then i want the system to display a messagebox when im opening up a form. If today is not in the range then the messagebox should not pop up.
Code: If DLookup("HollidayFrom", "tblBuildingManager", "[HollidayFrom] >= Date()") And DLookup("HollidayTill", "tblBuildingManager", "[HollidayTill] <= Date()") Then msgBox "A building manager is on holliday" end if
I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.
I have a table (ComplaintsResponses) that has two fields, both text
[ShortDescription] [ResponseText]
The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load() If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True Else Me.CASETIF.Visible = False End If End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
