Abbreviating State Names

Jan 14, 2005

Hello out there:
We have a application that imports information from a csv file into our customer order table. After we process the order we have to export this inforrmation to our UPS shipping program to process the shipments.

Problem is that the UPS program only recognizes the abbreviations for state names. Is there a way to run a macro, code or update query that will change the state names to the correct abbreviated version before we do our export?

Thanks immensely,
Ivan

View Replies


ADVERTISEMENT

Field Names Don't Match Names On Form

Nov 7, 2006

I have a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.

However, I am getting the following message:

"some of the field names you tried to paste don't match fieldnames on the form"

and then not all data in all fields gets duplicated.

I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.

Thanks one more time, in advance!!

View 1 Replies View Related

Reports :: Repeating Row Names And Column Names

Jan 17, 2014

How can I repeat column names and row names on multiple pages of the report ?

View 1 Replies View Related

Unexpected State

Jul 13, 2005

Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?

View 8 Replies View Related

Determine State

Mar 28, 2006

I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.

State: IIf(Left([tblMainFrontierUnits].[ProjectID],1)="2","CA","NV")

What is wrong with this statement? I am getting a compile error.

View 5 Replies View Related

City State Zip??

Aug 26, 2005

Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.

View 8 Replies View Related

Check Box State

Dec 9, 2005

If the check box is null (grey) - the default value, I do not want to include it is the sql statement. If I use the code below I get the error 94 Invalid use of null. How else can I exclude this ststement is the check box is grey.

thanks

If CheckStaged Then
strWHERE = strWHERE & " AND s.Staged = " & CheckStaged
End If

View 2 Replies View Related

How Can The Value State The Date?

Dec 11, 2005

i have 2 questions.

1) how can i have a record automaticly fill in the next date from the privous record? (Example if record 1 has date:10/14/05 i want record 2 to fill in in that same feild 10/15/05?

2) how can i have a reccord automaticly fill in the same number from the privios record? (Example if record 1 has Number :34 i want record 2 to fill in in that same feild 35?


I keep on changing these numbers, so its vitel that it repeats the privous record

David

View 3 Replies View Related

Database Won't Open, Placed In A State.

Jun 7, 2006

I am getting the following Error:

"This database is in an unexpected state; Microsoft access cannot open it.

"This database has been converted from a prior version of Microsoft access by using the DAO CompactDatabase method instead of the Convert Database command on the tools menu. This has left the database in a partially converted state.

"If you have a copy of the database in its original format, use the convert database command on the tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database objects can't be recovered."

The database is on the server. Nothing was changed. Someone in our IT department says they simply opened it and then closed it. I think that they had their access program set to compact and repair on close.

I have tried to import the tables but am gettting the same errors.

I have the original database but it is an old backup that is missing data.

Can someone HELP

View 1 Replies View Related

Queries :: Get Top 10 NPI Per State And County

Feb 27, 2015

I am trying to get the Top 10 NPI's by State and By County based on the Current_Year_Members_Outreached. Below is the query I created but I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever.

My source table only has 67k records which doesn't seem like a lot. So, I have two questions:

1. Does my query below look correct based on what I am trying to achieve?
2. Is there a better way to right this query to get better performance?

Code:

SELECT
A.STATE,
A.COUNTY,
A.NPI,
A.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS A

[Code] ....

View 7 Replies View Related

Countries,state/province,region

Mar 3, 2007

Do you know of any knows list that contains cross-refs of all Countries,state/province,region so that I can build cascading combox for my site.

This is very strange. It is a concept we see all over the internet websites, but the least documented!!!!

View 1 Replies View Related

Combobox Country, State, City

Feb 21, 2007

Hi All!!

I need help with the code for a combobox. The thing is that I've created a form where the user has to select the country, then the state and finally the city. What I need is that once the user selects the country, the combobox of the state shows only the states of that country and then the combobox of the city shows only the cities of the chosen state. I'd appreciate all the help given. Andrew.

View 1 Replies View Related

Access Data To Display On State Map

Jan 15, 2013

I have a database will all the FM stations in the USA/Canada, I wish to plot the frequency and calls to the correct city on the state map as per the attachment, can it be done? If so, what are my steps and what maps can I use?

View 2 Replies View Related

Autopopulate City & State From Zipcode Table

Mar 1, 2005

I'm stuck on something that should be simple, but maybe it isn't:

Problem 1:

To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).

Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.

If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.

Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.

Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.

Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.

HenryTD

View 3 Replies View Related

Setting Checkboxes Back To A Cleared State

Jul 13, 2005

Apologies first if there is an easy answer to my problem.

I have set up an option group with 3 checkboxes with choices Yes, No, Maybe. On data entry you can click any of the three boxes but after clicking one how do you revert to a situation with all 3 checkboxes cleared.
I don't want to use a default checkbox as that would add another row but I just want to clear out the boxes so it looks as though none of them have been ticked.

I hope somebody understands my problem.

View 2 Replies View Related

Expression To State Current Balance After Each Transaction

Aug 24, 2004


I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?

Paul

View 2 Replies View Related

General :: State Of A Record Where Changes Made But Not Saved

Dec 8, 2012

i want to have a button where the operator can cancel when on a current record. i want an if. if form has changes made but not saved then undo else close.

is the record state 'dirty'?

View 2 Replies View Related

Modules & VBA :: How To Execute A DoCmd From Within A Connection State

Feb 24, 2014

I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have

1) To open the database exclusively:

Set connDB = New ADODB.Connection
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareExclusive
.Open strdbpath 'path to database
End With

2) To import from Access:

strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table
If connDB.State = adStateOpen Then
Set objRS = New ADODB.Recordset
objRS.Open strSQL, connDB, adOpenForwardOnly
On Error Resume Next
objRS.MoveFirst
If Err.Number = 0 Then
On Error GoTo 0
fieldCnt = objRS.Fields.Count
For fieldNum = 0 To fieldCnt - 1
ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name
Next fieldNum
ws.Cells(2, 1).CopyFromRecordset objRS
End If
End If

3) To start a macro:

DoCmd.RunMacro "Daily Import"

Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.

View 8 Replies View Related

Queries :: Sum Of Sales - TOP 5 Agents By State Over Given Date Range

Sep 5, 2013

I want to get the sum of sales of the top 5 best agents by state.

I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.

SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ
FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code
WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate]))
GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State
HAVING (((Debtors.State)="vic"))
ORDER BY Sum(filedata.NetTotal) DESC;

which returns

DebtorFullNameSumOfNetTotalStateCountOfBorQ1429ABC company147150VIC11360XYZ Company107204.55VIC32534MNO organisation68799.86VIC31168PQR business58934.67VIC31326FGH industries32480VIC2

(count of BorQ) is the number of sales

I have tried using UNION ALL and changing the State but that only caused syntax errors.

View 2 Replies View Related

Forms :: Form State AfterUpdate Differs Between Key And Click

May 8, 2013

In ACC2010: A subform has a control for date of birth with an AfterUpdate Event. When the date picker is used to populate the control the user must either click outside the control or press the tab key to trigger the control's AfterUpdate event. This event runs some calculations, the results of which are stored in a separate table. When the calculations are finished and focus returns to the form, the form's state depends on whether a keypress or a mouse click was used to trigger the event.

The form's state is indicated by whether calculated controls on the form are empty - mouse clicks leave contents as before the event, tab causes the contents to disappear. In addition, if a keypress is used the user must click twice to trigger the form's BeforeUpdate event. A single click is required to trigger that event if a mouse click occurred after using the date picker.

How to get thekeypress to behave the same as a mouse click?

View 2 Replies View Related

Modules & VBA :: Change Label To Display Full Name Of State On Form

Jan 1, 2014

I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?

[CODE]Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
lblTxtSt = txtState
If txtState = "FL" Then
lblTxtSt = "Florida"
End If
lblTxtSt.BackColor = 15658720
lblTxtSt.Visible = True
lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub /CODE]
D7

View 8 Replies View Related

Forms :: Grid-rows Not Displaying Selected-state Correctly After Many VBA Requeries

Mar 2, 2014

when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.

Code:
oListBox.Selected(lngRow) = True

However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).

Code:
Print oListBox.Selected(lngRow)
->True

Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.

Code:
oListBox.Requery

This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.

i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.

View 6 Replies View Related

Modules & VBA :: Mail Merge - Database Placed In A State By User That Prevents It From Being Opened Or Locked

Jun 19, 2014

I have the following code which errors on line

Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"

The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.

Code:
Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")

Dim objWord As Word.Application
Dim docWord As Word.Document
'Open MS Word using early binding.
Set objWord = New Word.Application
Set docWord = objWord.Documents.Open(strWordFile)

[Code]......

View 8 Replies View Related

The Database Has Been Placed In State By User "Admin"

Mar 13, 2007

hi,

Everyone know this error? the database has been placed in state by user "Admin"!

We are using a shared database in Access 2003 that is still in Access 2000
format. When more than one user has the database open (by using the vb.net), the following error
message appears "The
database has been placed in a state by user 'Admin' on machine XXXXX that
prevents it from being opened or locked."

How to slove this error? please help! Urgent!:confused:

View 3 Replies View Related

Names

Apr 19, 2005

I have a feild that requires firstname and lasatname for input.

How do I set it up so that is will takie the first name and Capitialize the first letter and do the same for the last name.

Ex: enter: john smith

needs to be: John Smith

Same thing for Company name

ex: the best company
need: The Best Company

I need it this way for a mail merge document I need to use

View 4 Replies View Related

Parsing Names??

Apr 11, 2006

I am trying to do the following
If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows

1.1 Gul/Mohammad Atif Mr 2.1 Gul/Mohammad Adil Mr
3.1 Bond/James Mr

I have to copy the above data from the abacus tcketing system into a text box.
My question is how can i devide the above three names into three seperate text boxes.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved