I have a Contacts DB and at the moment I enter Cities/Towns and Post (zip) codes separately. I now want to set it up so I can select a city or town and have the appropriate post code fill it's own field without my intervention. Is this a case of cascading combo's or can I just bind a post code field to the city/town field? Can someone point me in the direction I need to go please?
View RepliesHello Don,
View 13 Replies View RelatedHi,
I have built a very simple database to record names and contact details for all enquiries coming into our business. What I want to do is create a means whereby anyone in the company can enter a name of a specific town or county and the DB will produce a list of all enquiries that have originated from that town or county. Please write any replies in simple easy to understand steps as I am not an Acess expert. Any help would be appreciated.
Best regards
Keith:)
i have created tables as provinces, districts and sub-districts in my other forms say member i have create a look up wizard to the provinces, districts and sub districts. every province has multiple cities and every districts has multiple sub-districts my question is how to set properties so that if i select a specific province all its districts should appear in district field and districts of other provinces should be filtered. a good example is on some websites when we select a specific country all its cities or states appear in city field and if we change our option to some other country city drop box also changes to the cities of that respective country...
View 2 Replies View RelatedI have a table that contains a Zip Code field. Some of the Zip Codes are missing the leading 0. Zip Codes are a 5 digit field and these codes only have four. how do I get Access to recognize the number of characters and then add the leading 0 to only the ones that have 4 numbers?
Thanks
Has anyone had any dealings with a way to calculate distance between 2 zipcodes? Of course all the zips wouls be in the current DB.... Then need to query those within a certian distance of another zipcode. I have a feeling this will need to be linked to an external source or app........ BUT... Anybody have any experience with anything similiar?
View 5 Replies View RelatedI would like to print a bar code on my invoices and then have my delivery drivers run the invoice under a bar code reader when they return from delivering. I know how to print the bar code I am nit sure how to go about interfacing a bar code reader in access 2000. Any thoughts?
View 3 Replies View RelatedHi i have designed a database where the users use a combo box to find a particular code they want..for example dog_brown or dog_grey, on the report which i have produced this code for the type of dog shows up to. I have another table which has a column with the code and also the description...eg dog_brown...brown dog. My users have asked me if on the report which i have produced can the description be shown instead of the code. I dont know if this can be done...any help would be grateful. Thanx
View 3 Replies View RelatedI was wondering where I might get a list of code I can use to change text in my program. If there is no list what would I use to get dark green. Right now I am using the following:
[description of failure].ForeColor = vbGreen
Thank you
roborro
I have been wanting to ask this question for long. Is it alright to put too many codes in the various events of the forms and controls? Will it affect the efficient functioning of Access? Will those codes result in any absurd behaviour? For example, I have DMax function in the BeforeInsert of form followed by three If....End If codes. I have not faced any problem so far. However, I just want to hear the opinion of the experts.
View 1 Replies View RelatedHi, I have the following problem.
I have three tables, Main Table, Table A and Table B. All three tables have a field called "Cutoff Time". The Main Table will look up the info from Tables A and B. Table A has a field called "Service". In the main table, there is also a field called "Service", which is same as the field in Table A. The condition is this: The main table should use the "Service" field to match the "Service" field in Table A to get the "Cutoff Time" from Table A. If there is no such match, then the "Cutoff Time" will be taken from Table B using another field called "Station".
I was wondering, how can I perform such an operation? Do I need to write SQL statements?
Thanks in advance!
I would like the textbox named "text7" to show the word "keswick" when the text inside the "txtUsername" is KH and show the word "Lawrence" when the text inside the "txtUsername" is LH. What sort of codes should I write? should it be SQL or Expression Builder or Code Builder? Thank you=)
View 2 Replies View RelatedSometimes the simplest of questions can be the hardest to find the answer to. I have spent the last 2 hours searching for the answer to this one. I'm sure it's on this site somewhere but the search is running way to slow for me to be of any use.
What exactly does the numeric color codes in VBA represent? I have downloaded a score of color pickers. utilities and none use these numbers in the way Access does. Ideally, I'd like to find a utility that will allow me to specify a particular color and then give me the corresponding code for that color.
TIA
Does anyone out there have any ideas on how I can get a full list of the error codes and their descriptions for microsoft access?
I've set up a simple table with two fields, one for the error code and the other for the description. I've written a short bit of code (see below) to build the table for me, but it only generates 88 codes with the highest being 746.
Now I know that there are error codes up in the 3000 area because I'm trapping a few of them.
What I'm looking for is a list of the error code so I may write an extended list in my error trapping routines.
Code as follows:
Private Sub Form_Load()
Dim My_RecordSet As DAO.Recordset, My_DataBase As DAO.Database
Dim My_Error As Long
Dim My_Description As String
For My_Error = 0 To 4000
On Error Resume Next
Err.Raise My_Error
My_Description = Err.Description
If My_Description <> "" Then
If My_Description <> "Application-defined or object-defined error" Then
Set My_DataBase = CurrentDb
Set My_RecordSet = My_DataBase.OpenRecordset("ErrorCode_Table", dbOpenDynaset, dbAppendOnly)
With My_RecordSet
.AddNew
![ErrorCodes] = My_Error 'set error #
![ErrorString] = My_Description 'set error description
.Update
.Close
End With
End If
End If
Next My_Error
End Sub
Cheers muckers
D
I am working on a membership database that only has members from Canada and the United States. In the zipcode field, is it better to have two seaparate fields or is there an input mask that can handle both US and Canadian zip codes.
Thanks in advance.
24t42
Hello,
I was wondering, is there a way to comment out some of your SQL codes in Access querries, like you do in Oracle or SQL Server or VB?
I need to know how to write comments in my SQL codes when usingh access
I have a list of codes:
K101
K101A
K101B
K101C
K101D
K101E
L101
L101A
L101B
L101C
L101D
I want a query that displays all codes that dont have a letter on the right.
I have a report bound to a query that includes zip codes as a field. Zip codes are formatted in the table as 99999-9999 and display in this format when the query is run. However, when I place that field in the report text box as
=[ContactCity] & ", " & [StateAbbreviation] & " " & [ContactZip]
or even as [ContactZip]
The zip is not formatted.
I am using Access 2010. I have 4 columns in my file. They are "Code", "Item", "Qty", and "Amount" . I have 5 products with my own codes. It means the codes are set by me. For example, 00001 for ruler, 00101 for book, 00201 for pencil, 00301 for rubber, 00401 for pen. My wish is to link the code and product together. It means that when I sell a pen, I must type the code for pen (in the "code column") and I must type pen in "Item Column", so I think it really takes long to do this because I will really make a mistake because the code and item are not linked at all. What I mean is that when I fill or know the code, I will get the product automatically in the "Item Column" or vice versa.
Now I have set the code 00401 for pen.
So when I need to fill the information. I wish to fill the code in the "Code Column" and then the word pen is automatically shown in the "Item Column". In this case, I will be able to get right code linked to its product.
I have a very involved question regarding the export of data from and Access front end, SQL back end into multiple Excel Worksheets.
Where is the best place to post this in order to get maximum exposure to the great minds that cruise this board.
Regards
I have a form that has several multiple choice question and i have also designed a table and update queries for the DB so far i can enter the values and then have them changed from the original to the values requested by my bos however i also need to find a way to make a report that will allow me to shot the percentage of people that chose each individual answer with anywhere from 2 to 10 possible choices on each question i see no logical way to do this....... help!
View 1 Replies View RelatedHello every one
I am new here and this is my first post
I am trying to make overtime record for my company
I use the following feilds
field name .................... format
Start_Time ................... short time
End_Time ..................... short time
Total_Over_Time ........... short time
now when i use
Total_Over_Time = [End_Time]-[Start_Time]
I got correct answer
but
while i am trying to make monthly report
if Total_Over_Time is greater then 24 hours it shows me 1
means for 25 hours is shows 1
Please help me to overcome this problem
Thanks
Pehalwan
I am developing an Access (2000) database to provide reporting data gathered from SAP and make it easily accessable for 12 analysts. The data set is HUGE, 5 months nearly a gig and will need at least 2 years worth, so I don't see any option other than using SQL as a back end. I have experiance using SQL server as the back end but have never had to develop my own server.
I do not have access to an existing server but do have permission to purchase SQL software, this is where I need the help. If anyone has suggestions, advice, links or other information I would be greatly appreciative.
Thanks in advance for your help.
“where I added information”
A lot of you have been reading this but no help.:eek: Did I use a poor title? I have used SQL as a back end but really need to know how to produce one out of thin air.
I ordered SQL 2005 off my company's IT store but when reading further it needs a special operating system, I think. Is this true? What do I need to have in order to provide a very large data set to a bunch of barely computer literate analysts?
These people are excited when they realize that vlookup can do more than one thing!! The concept of a union query would blow them away. I am frustrated with the competency of my co-workers but need to give them something that is idiot proof without letting them know that this is the object.
Currently they are having an SAP download sent to a specified share drive and running an Excel “macro”, moves a couple of columns an such, that puts it into a format that is semi-usable. This is totally inefficient for their needs but they don’t know it as yet.
What I am asking is how to take a fairly robust pc and make it a SQL server that will service a dozen or so people. What kind of operating system is needed and what sort of admin rights will be necessary to allow access to the desktop so they can get their data?
The IT help desk is very scary, not really understanding the requests presented. I haven’t been with the company very long but what I have garnered disturbs me I don’t believe the IT people have a clue.
I’ve been an Access developer for about 4 years now and know this is not an insurmountable obstacle. I am confident that SQL is the solution but haven’t ever had to create it from scratch before. Please, links or other suggestions are MOST appreciated.
I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.
I tried to run the following statement in my VBA code to update a table in my database:
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)
Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?
The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.
Dim rstDataLoad As New ADODB.Recordset
With rstDataLoad
Set .ActiveConnection = cnnSHELL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;"
.Open
End With
'Calculate Retire_Date
rstDataLoad.MoveFirst
Do While Not rstDataLoad.EOF
Calculation omitted.
rstDataLoad.MoveNext
Loop
rstDataLoad.UpdateBatch
rstDataLoad.Close
Set rstDataLoad = Nothing
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
I have a query Im working on in the query are several fields. 2 fields (cat1 and cat 2) have nothing but 3 digit category codes (ex- 333, 334, 335 in the and some null values. Another field is the quantity of items I have which must be >0.
I have 7 codes I want to delete from cat1 and cat2 so my guess is I would place Not "333" And "334" And "335" in criteria for mcat 1. I also need to do the same for cat2. So I would apply the same criteria in the same row under cat2. So heres how my query would look in design view.
cat1 | cat2 | quantity
Not "333" And "334" And "335"| Not "333" And "334" And "335"| > "0"
Its not working, I can only seem to do one "Not" in all of the fields. So If I do Not "333" in cat1 I wont be able to do anything in cat 2 nor will I be able to add 334 to cat 1 or 2. I have tried Or it doesnt work I have tried using different colums and different rows.
There has to be a way to delete the same criteria from 2 different fields for multiple items per the same field.
Any thoughts? Much appreciated!
I have to create codes with a certain algorithm.
It consists of 6 digits (positions) and looks for example like this: 6D45F3
On each position the sequence is "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", so it has 16 possibilities.
Beginning from the last position going up to the first position the positions change according to the above sequence and starts from "0" when it has reached "F".
In the example above the next codes would be
6D45F4
6D45F5
...
6D45FF
6D4500
6D4501
...
6D450F
6D4510
...
So after each 15th loop the next positions changes and the previosu set back to "0". I thought that this should be easy but I failed with this code.
var16Stelle = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F") '16 Elemente
strErsterBC = UCase(InputBox("Enter the first code.", "Barcode-Generator")) 'for example 6D45F3
intRow = InputBox("Entere the number of codes to create.", "Barcode-Generator")
intStart6 = ListIndex(Mid(strErsterBC, 6, 1), var16Stelle)
intStart5 = ListIndex(Mid(strErsterBC, 5, 1), var16Stelle)
[Code] .....