Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 2nd, 2007, 12:25 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Convert Excel to Access

Hello all! This is my first post on this here fine forum, and im hoping to get some help!

When i started at my job 3 years ago, i took it upon myself to convert the job book into electronic format. I knocked up an excel spreadsheet, and ever since then, any job we've quoted has gone into this spreadsheet. It's now quite big, around 5mb in size, and is becoming a nightmare to handle / search / open!

Somebody told me to put the lot into an Access database, and then write queries to help it along! Sounded easy enough!

Now that i've imported all my data into Access, i have a few questions!

1) Primary Keys. I'd like to have the job number as a primary key. Our job numbers are individual 4digit numbers and go up in 1number increments. But the spreadsheet starts at 4595, and as hard as i try, i cannot get the Primary Key to start at 4595. Any ideas?

2) Date's. On the spreadsheet, I'd have 4 cells. The first had the date we recieved a quote. The second cell displayed the return date for the quote. The Third cell then showed the date we returned the quote. And the forth cell, had a formula, along with some conditional formatting, which showed whether the tender was returned in time or not..

When importing my spreadsheet into Access, all the dates went into a strange format. The number is a 5 digit number, and typically looks like '39105', (which i beleive is the number of days since the year 1900). How do i change this number into a date format that access will recognise?

Secondly, how can i get the formula's to work (taking one date from another, and checking against a third?).. or are formula's strictly for Excel?

Any help would be greatly appreciated!!! I can see how Access will come in handy!

Reply With Quote
  #2 (permalink)  
Old February 2nd, 2007, 12:52 PM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

1)What i have learned from the great people here is that you should put an autonumber field in that way you can assign your own Job#

So I think you want to add a row to your table that says "JobID" make this this Autonumber and make it your Primary Key.

Make sure that when you relate it to other tables it has the Foriegn Key

Try and see if some of the posts i have in here help because I have asked the same questions. It looks like we are trying to do the same thing.

2) youll be making a query with Criteria I am not sure what to put in there but thats what i know.

Your date format problem.
This is for the future make sure you put an "Input Mask" in the Properties of that field the "Input Mask Wizard has a couple of selections for both entry and saved format.


Reply With Quote
  #3 (permalink)  
Old February 5th, 2007, 06:49 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

scandalous, thanks for your reply!

1) I'd rather use Autonumbers, as it's exactly how our job numbering system works anyway! Problem is, my autonumbers start from 1. Where as our job numbering system (for this purpose anyway) starts at 4595. How can I get the autonumber to start from 4595 without creating 4594 empty records at the beginning?

Also, what's a foreign key?

As for the dates, i'll have a look at teh input mask wizard.. I'm not sure how this'd work though! I'll give it a go, thanks :)

Reply With Quote
  #4 (permalink)  
Old February 5th, 2007, 08:59 AM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

I think you have the same confusion about what the autonumber property is for. I had the same problem. I posted the exact same question in the Access VBA forum and this is what I was told:

===========================
These are called "Regular Expressions" and MSDN has a whole series of articles on them.

Basically what you do is create a public variable (As String) again in a Module.

Then concatenate the values you want in the string you are passing. So the On Click event of the button you use to open the new form would be this:

Dim dtDate As String
Dim sSalesID As String 'it looks like

dtDate = DatePart("d", Date) & DatePart("m", Date) & Right(DatePart("yyyy", Date), 2)

sSalesID = Me.SalesIDFieldName

'Assume Public variable name is pMyString

pMyString = "Q-" & dtDate & sSalesID & ...

I am not sure where you are getting your number sequence from. Where is that coming from? If you are generating it, what is it based on? Number of quotes that day, or number of quotes ever?

Anyway, now that you have the public variable value, close the first form and open the second.

The second should open to a new record. On the Second form's On Load event, put this:

Me.QUoteNumber = pMyString
===========================

You need to know that the autonumber as a primary key is pretty much only used by access to insure that a unique ID is used to relate to other tables.

a Foriegn key is when you place the Primary Key of one table in another that has a different Primary Key to relate the two tables.

I would suggest at looking at all of my recent posts because I think they will help you. Your trying to make the same thing I am with a little twist.

Make sure you look in the Access VBA forum on here too because they have alot of helpful things. If you are trying to do something that you dont know what its called, I found it helpful to describe what your trying to accomplish and ask what its called.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert text from excel to proper date in access bprodman Access VBA 1 June 18th, 2007 03:48 PM
Convert Excel to PDF shivavalkyre Beginning VB 6 1 March 15th, 2007 03:44 PM
Convert an Excel worksheet to xml bluesockets XML 1 November 23rd, 2006 10:46 AM
Error Data When Convert Excel to Access rpitoyo Pro VB Databases 0 August 5th, 2003 12:05 AM



All times are GMT -4. The time now is 04:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.