Dashes Are Causing Problems Retrieving Data
Aug 2, 2006
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.
View Replies
ADVERTISEMENT
Nov 13, 2007
Hi,
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?
Thanks
Ket
View 1 Replies
View Related
Feb 14, 2005
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?
View 5 Replies
View Related
Dec 15, 2005
Does anyone know how to setup an ODBC to the Ebay Web Server?
View 3 Replies
View Related
Feb 16, 2005
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?
.................................................. .................................................
Ans:
It would be nice if you could find out the formulae for working out the pence per mile bit based on the cc.
Either way, I think your table needs changing to this design
Price. . . . CC. . . . . . . . . . . . . VAT
75.0 . . . .1000. . . . . . . . . . . .8.525
75.0 . . . .1000 to 1500. . . . . . .9.653
etc
etc
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. .................................................
Thanks,
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.
.................................................. .................................................. .
Sharon, you could have the price per litre in one ComboBox and the engine size in another. Both these would then be the criteria for a query.
Can you post your Db and I'll have a look for you, failing that I could knock one up (in Access97)
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. ..................................................
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!
Thanks
Sharon
.................................................. ................................................
I can't seem to get the file to attach it doesn't seem the right extention code it's an mdb
View 4 Replies
View Related
Nov 18, 2005
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?
Thanks
View 1 Replies
View Related
Jan 18, 2008
Hi All,
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.
Hope this makes sense!!
many thanks,
Nigel
View 1 Replies
View Related
Dec 13, 2007
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").
View 1 Replies
View Related
Aug 25, 2015
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
[code]....
View 10 Replies
View Related
Aug 30, 2006
Hi Folks,
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?
Any help is greatly appreciated.
Thank you very much,
Joe
View 3 Replies
View Related
Aug 3, 2005
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'd be grateful for any help!
Cheers
Dave, England
View 2 Replies
View Related
Jul 31, 2005
Hi,
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.}
I could use some help, fast!
Thanks in advance,
David
View 1 Replies
View Related
Jul 3, 2014
My parameters are linked to a form and say:
[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...
View 7 Replies
View Related
Nov 15, 2014
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.
View 2 Replies
View Related
May 1, 2007
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)
01-AB134
02-091
2-2177
3-715
03-021-A
05-001-PBPL
07-07-955
View 4 Replies
View Related
Dec 12, 2006
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
UPDATE stihl SET [part number] = left([part number], 8)& "-" & mid ([part number], 9);
the problem this adds dashes after numbers shorter than 11 and 2 dashes if on 4 long.
Any ideas thanks in advance
View 1 Replies
View Related
Dec 4, 2007
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! :)
View 14 Replies
View Related
May 8, 2013
I need to create a Autonumber field in a table with the following starting value 13-642-2000.
View 5 Replies
View Related
Aug 7, 2014
Is there a way to convert a phone number in text format into a number and remove any dashs or parenthesis. What function can I use ?
old format (951) 244-3011
new format 9512443011
View 2 Replies
View Related
Aug 14, 2015
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.
View 4 Replies
View Related
Sep 28, 2006
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.
:(
View 2 Replies
View Related
Apr 20, 2006
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.
View 1 Replies
View Related
Sep 26, 2006
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
View 2 Replies
View Related
Feb 5, 2008
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?
View 1 Replies
View Related
Nov 5, 2006
Hello All,
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)
Cheers,
Aaron
View 2 Replies
View Related
Mar 24, 2008
Hi,
In my database if the user wants to change a record, I make them store the old record in another table. I am using the following code.
Private Sub cmdArchive_Click()
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim i As Integer
Dim s As String
Dim sSQL As String
sSQL = "INSERT INTO tblNamesArchive ( txtGivenName, txtSurname )" _
& "VALUES ( '" & txtGivenName & "', '" & txtSurname & "');"
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?
Thanks
Raghu Prabhu
Melbourne.
View 2 Replies
View Related
