I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
I am new to access and need help trying to retreive data. Basically I have a unique ID field (123545). I what a user via a form to be able to retreive data using this unqiue ID. Does anyone know the best way to achieve this in access please?
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below (although the table headers have moved a bit). There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litreUp to 1000cc1001 to 1500cc 75.08.5259.653 75.28.5509.682 75.58.5759.710 75.78.6009.738 75.98.6259.767 76.19.6509.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
I put this on the tables forum but my answers have now stopped, can anyone here help me with how I get this information to appear on a form....
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below. There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litre ....Up to 1000cc.......1001 to 1500cc 75.0 ..................8.525.................9.653 75.2 ..................8.550.................9.682 75.5...................8.575.................9.710 75.7...................8.600.................9.738 75.9...................8.625.................9.767 76.1...................9.650.................9.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
Unfortunately I can't get hold of a formula. I'm still not sure how I would look up a value, even if I changed the table as you suggested. The user would need to select a cc size and then a price per litre which would then need to be cross referenced to give a value. I could set up different tables for each engine size, but then I'm not sure how I could point the answer at the correct table. I don't even know if what I am trying to do is possible in access.
I think I've attached the file, but I've never done this before so it might not be there!
The table I'm trying to create is called pence per litre but it is completely stand alone at the moment until I can work out how to get any information out of it. I have changed the table to your suggested layout but have only entered a few records, there are hundreds to be entered if it can be made to work!
In my employee attendance database each record contains an employee id#, a number corresponding to an attendance infraction, and a corresponding date. Each week a clerk queries the database to pull up records for all employees who have a yearly 'total number' of infraction values over a certain numerical limit. Any suggestions as to what is the best way to: 1 - Calculate that yearly 'total number' for every employee. 2 - Retrieve the date of the most recent attendance infraction for each employee that has a total value that is over the limit?
I need to make a payment based on the latest Verify Number to a specific person so i am trying to create a form that is focused around a person, looks up the latest Verify number and can make a new payment number to add new payments.
In my tables, it works perfectly excluding the latest Verify number whereas i do not have any filters set. The Verify numbers can change which is why i need to make a new payment based on the latest Verify number. Using this number, i can add many payments to a Payment Number and add many Payment Numbers to a Verify Number for example:
John Verify NumberGo4566546 Payment Number 44 Payments101Work carried out £800 102Delivery costs £100 103Material Cost £400 Payment Number 49 Payment 168 Work Carried out £700 170Work Carried out£450 Verify Number Go4566952 Payment Number 50 Payments171Work Carried Out£900 177Work Carried Out£500 Steve Verify Number Go5877654 Payment Number 51 Payments178Cleaning£120
My Tables are linked as follows: Person Table( name of table ) PersonID( unique ID of that person )
Verify Table( name of table ) VerifyID( unique ID of the Verify Number ) PersonID( linking to Person Table )
PayNo Table( name of table ) PayNoID( unique Payment Number ) VerifyID( linking to Verify Table )
Payments( name of table ) PaymentID( unique Payment ID ) PayNoID( linking to PayNo table )
The Payments figures have no relevance as they are numbers given by the person to me so i do not need to link them, i only need to link the table they are entered onto.
I am trying to get this onto one form whereas i can see who i am paying, the latest verify number, the last payment number to the person and the last payments in a table. Then, i can click add a new payment number, and i can add a new set of payments t the newly created payment number.
I have a table (Working_Year) that is storing a single field which is the year (current_year) that is selected by the user in my initial form when the database is loaded. Am I able to use that data in other forms and reports with out linking the table? I would like to simply display the current_year the user is working in a textbox somewhere on the forms (example form named: "Main Entry") and use the current_year as a filter on reports (example report called "Gifts - by Type").
i have the below code which inserts data and then i want to retrieve the value in the first two columns but firstly it says "you must use dbseechanges" but i have put this into the code you may see below,
Code: Dim db As DAO.Database Dim rs As DAO.Recordset dim sSQL as string
I am creating an IF CASE statement in SQL that basically takes the 2 last characters in a field and if they happen to be 'HX' then a special rate is used to calculate the cost whereas if their is no 'HX' as the last 2 characters of the field then a different rate is applied.
(Right([BOARD SPEC DATA],2)) has always worked flawlessly in SQL to render similar solutions. However, unfortunetly the weekly extract that comes into us permits for massive amounts of blank but real spaces after the data in [BOARD SPEC DATA] field so what ends up happening is that (Right([BOARD SPEC DATA],2)) yields a (SPACE SPACE) value even thou many of these values should be 'HX' as they are the last 2 real non-space values at the end of the field.
If there any easy way to fix this keeping in mind that this is how we get the extract everyweek so a search/replace on spaces won't work ... is there any way I can use a Right command but ignore spaces so that it simply renders the 2 real characters at the end of the field as oppossed to blank spaces?
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable r = 55 ' the start row in the worksheet Do While Len(Range("I" & r).Formula) > 0 With rs .AddNew .Fields("Quote_Price") = Range("I" & r).Value .Update ' stores the new record End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
I have a query with a field defined in the query as follows:
Next Bill Date: IIf(IsNull([dtmLastBillDate]), DateSerial(Year([dtmDateRecd]),Month([dtmDateRecd])+1,15), DateSerial(Year([dtmLastBillDate]),Month([dtmLastBillDate])+[lngNumMonths],15))
I can't seem to set any criteria for this field without getting an error: 'Data Type Mismatch in Criteria Expression'
Examples of criteria that I've tried: =#1/1/2005# >#1/1/2005# =Date() Year([Next Bill Date]) = Year(Now())
All of the above generate that same error. {I've seen many pages/posts regarding 'data type mismatch' but none seem to relate directly to this situation.}
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null
Ordinarily this works fine in returning either the selected value or all values if left null.
I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].
The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.
If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.
The crosstab data isn't specifically the problem but needing to set the parameter names seems to be
I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels
It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...
I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.
I have a db, that I used to maintain as a end user, now it's in a multiuser environment, problem is the user has to enter job numbers. Below are all valid job numbers, therefore a mask will not work. So I was thinking "HOW" could this be done, perhaps eliminating Spaces, underlines and dashes from this field. How would I do this on the same field for all 3 conditions (course i would have to backtrack)
I have a very large database with record that are from 3 to 11 characters long. I would like to run a query to inset dashes at position 5 and 9 so a part number would look like this 0000-000-0000 or following the same convention but never dashes as the last character this is what i currently have UPDATE stihl SET [part number] = left([part number], 4)& "-" & mid ([part number], 5); then run a second as follows
I know this is probably the dumbest thing to ask, but I am just not getting it. I'm tryin to run a Query, and I assume it's a SELECT query for Last Name, First Name, SSN. The tbl containing this information [tbl_Employees] automatically places the dashes in the SSN through an input mask. However for one type of report I will later need to generate, the SSN cannot contain the dashes. What is the criteria for this?!? I am so lost! I know it's probably something so simple that I'm just overlooking it, but I'm clueless. Can someone help me out? Thanks so much! :)
I have a distributor table within an existing database that I use to keep track of quotations to each distributor for each project our distributors bid. It was simple to design 5 years ago when we only had to quote one mfg to each distributor. However, I now have to quote distributors multiple manufacturers and tried to develop a second "estimate" table to handle things. The issue arises when I want to view all the quotes I have for one distributor, no matter which manufacturer is involved.
I have set up a relationship between the [Distributors] table and [Special_lite Dist Quotes] and the [Distributors] table and [tbl-Unassoc-Quotes] as shown in attached (.jpg)
My intents is to go into the Distributor main form and click on an estimate button that will open a form to show all the estimates I have given a particular distributor. I have been able to do that when I only use the (1) [Special_lite Dist Quotes] table. However, things get pretty screwed up if I try to add the second table [[tbl-Unassoc-Quotes] to the underlying query.
I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped. :(
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
I have a DB in .mdb format which works perfectly and when all the code is compiled it results in no problems. However. When I convert this to a .mde file, it doesn't work at all and it suggests that there are compile errors where there weren't any before! Please can anyone suggest what the problem might be as I don't particularly want to run this as a .mdb. Thanks
I have an Access2003 database that contains a table. The table has 2 fields. One is a counter and the other stores a picture which is datatype ole object. I want to do a one time export of the pictures. I want to save the pictures as jpegs in a designated directory. I know very little about working with ole object datatypes. Can someone tell me the easiest way to do this?
Is there a way to retrieve the very last entry to a table (via a query) without passing a value to the query.
Lets say I have a table Pets
ID desc ============== 1 Dog 2 Cat 3 Lizard
For example lizard was added last, is there a way I could pull just this out using a query? (keep in mind that I don't know wahat the last entry is, so I cannot pass any kind of value to the query)
Debug.Print sSQL Set conn = CurrentProject.Connection conn.Execute sSQL GoTo ThatsIt ErrorHandler: Select Case Err.Number Case -2147217908 'command text not set Case -2147217865 'cannot find table Case 3021 'no records Case Else MsgBox "Problem with cmdArchive_Click()" & vbCrLf _ & "Error " & Err.Number & ": " & Err.Description End Select ThatsIt: conn.Close
End Sub
I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?
