Access 2007 - Cannot Display 17 Digit Number

Aug 8, 2011

I can't display a 17 digit number in my table without losing the last couple of digits to 'rounding'. I've tried 'doubling' the field size but to no avail....whatever I attempt loses the last couple of digits to a 'nice round figure'.....

The numbers had initially been imported as text...which is really what they ought to be as they're identification numbers, but I had some issues using the find 'duplicate values' query and conjectured that was because the ID numbers had been defined as text---->though I could be wrong.

The VAL function works (to convert the text to numbers) but again -- I lose the detail of the last couple of digits.

I've been beating my head against this wall the entire day and at the very least, would like to know if what I'm attempting is viable. I've stumbled through function queries (with some success) and react like a deer in the headlights when it comes to VBA....

View Replies


ADVERTISEMENT

Display Double Digit Number

Mar 19, 2008

hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?

(Day([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Month([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Year([HTRXTBL].[HTRX_TIMESTAMP]) & " " & Hour([HTRXTBL].[HTRX_TIMESTAMP]) & ":" & Minute([HTRXTBL].[HTRX_TIMESTAMP]))))

View 6 Replies View Related

Access 2007 Display Image Options

Jan 11, 2013

My end goal is to display an image for every row on a report and/or form.I reviewed the various options for that, and finally decided the best thing to do would be to put the images in a separate folder in my PC, and put the path for the image in a column in the main(primary) table.In my form or report, I put an Image Frame, and put the ImagePath column in the Control Source. All is well and good.

However, I realize the inherent problem with having the full path in every single row, since I could move the image folder and I'd be hosed.So, I created an Environment Variables table, and I have a column called ImageFolder. Then I changed the name of the column in the primary table to ImageName instead of ImagePath. My hope was to be able to concatenate ImagePath and ImageName as the Control Source on the form and report.

I couldn't figure out a way to do that, since they are in two different tables, with no direct relationship between the tables.For the report, I created a workaround by creating a query of all the fields, including a field which concatenated the path and the ImageName. But since I use the form/ sub form to update the records, that wouldn't work for the form. My next attempt was to try to add an ImagePath back again to the primary record, but to have it default to the value that's in the EnvironmentalVariables table.

View 4 Replies View Related

Adding Another Number Digit Into My Fax Database.

Sep 3, 2007

Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.

How can I change all the numbers together?

Example from : 1234567 to 61234567

Thanks.

View 14 Replies View Related

Queries :: Convert 19 Digit Number To Hex String

Mar 4, 2015

I need to run a query that converts a 19 digit number stored in a Short Text field to a hex string

Example of the string is "3310854670615838865" the result should look like "2DF284C801000091"

View 1 Replies View Related

Truly Urgent - Need To Generate Random, Nonduplicating 9 Digit Number

Sep 28, 2006

Hi.
I have major report due within hours.
I need to add a field to existing table that will be a 9 digit number, non duoplicating or - to take an existing field of 9 digits and allow it's values to change to random or incremental, non-duplicate numbers of 9 digits. I already have an autonumber field I must keep.

Russ

View 2 Replies View Related

Refernece Number Missing Digit During Form Transfer

Dec 6, 2006

i have a jobref (pk) as text at the moment because i tried it as Number data type and had a problem..a reference number is made and when the date loses focus the refno is displayed in its textboxthis makes that refnoPrivate Sub cbojobdate_LostFocus()Dim maxRef As Variant, maxID As IntegerDim codeDate As String, maxDate As String codeDate = Format(cbojobdate, "MMYY") maxRef = DMax("jobref", "job", "jobref like '" & codeDate & "*'") If (IsNull(maxRef)) Then 'test for new month maxID = 0 'reset id to 0 Else maxDate = Left(maxRef, 4) 'get date code maxID = CInt(Right(maxRef, 4)) 'convert to int End If Me.cbojobref = codeDate & Format(maxID + 1, "0000")End Sub8 digits.. so a job on september 6th 2006 will have a ref no of 09060001a job on february 1st 2006 will have ref no of 02060001when i send the jobref to another form using this..Private Sub cbojobfrom_AfterUpdate()If Me.cbojobfrom = "t1" ThenMe.cbojobfrom = "LHR - T1"End IfIf Me.cbojobfrom = "t2" ThenMe.cbojobfrom = "LHR - T2"End IfIf Me.cbojobfrom = "t3" ThenMe.cbojobfrom = "LHR - T3"End IfIf Me.cbojobfrom = "t4" ThenMe.cbojobfrom = "LHR - T4"End IfIf Me.cbojobfrom = "h" ThenMe.cbojobfrom = "LHR"End IfIf Me.cbojobfrom = "ga" ThenMe.cbojobfrom = "Gatwick Airport"End IfIf Me.cbojobfrom = "gn" ThenMe.cbojobfrom = "Gatwick North"End IfIf Me.cbojobfrom = "gs" ThenMe.cbojobfrom = "Gatwick South"End IfIf Me.cbojobfrom = "st" ThenMe.cbojobfrom = "Stansted"End IfIf Me.cbojobfrom = "lc" ThenMe.cbojobfrom = "London City Airport"End IfIf Me.cbojobfrom = "lu" ThenMe.cbojobfrom = "Luton Airport"End IfDoCmd.OpenForm "job_cash_inflight", , , "[jobref]='" & [jobref] & "'"End Suband the job_cash_inflight form loads it with this..Private Sub Form_Open(Cancel As Integer)Me.[jobref].DefaultValue = Forms!job_cash_single![jobref]End Subthe problem is when i choose february the first for example02060001.. when the reference number transfers to the incoming flight form.. it displays as 2060001can anyone see why because before it transfers the refno.. the original job record has already been saved.. maybe when the id is being created it is not the best way.. or it doesnt match the text datatype.. if that is the case what number datatype shall i use in each of the tables jobref appears in?

View 14 Replies View Related

Modules & VBA :: How To Change A Number Column In A Three Digit Percent One

Jun 19, 2014

How can I change a number column in a three digit percent one?Tried the following, however it just deletes the content of the column:

Code:
Public Sub TEST()
Dim a11 As Double
Dim year As Integer

[code]...

View 6 Replies View Related

Tables :: Column To Autofill Based On First Digit Of The File Number

Dec 26, 2012

I have a datasheet with 7 columns. Two of the columns I'm working with are listed below.

File # Region #
2DE2-12345 2
3DE2-@@@ 3

In data sheet view I would like the Region # column to autofill based on the first digit of the file #. So, if file # entered = 3DE3-@@@@ then 3 would auto fill in Region Column. (data is entered in data sheet view)

I have used Left ([File #],1) to get value but cant make it auto populate the Region # column......

View 6 Replies View Related

Queries :: Access 2007 - Update A Field In A Table With A Random Number / Long Integer

May 27, 2014

Is it possible to run a SQL command to update a field within a table with random numbers?

More specifically - random long integers linking back to an ID (autonumber) field in another table?

Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)

The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.

So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.

I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.

View 6 Replies View Related

During Merge Access Is Dropping A Digit

Aug 16, 2007

Ok here is my question. I have two column that I am trying to merge. The first column I have set to be two digits. The second column is set to be three digits. The problem is if the first digit in the second column is a 0 it gets dropped once the two columns are merged. How do I stop this from happening. I need the merged columns to be 5 digits.

Thanks,

View 1 Replies View Related

Access 2007 Crashes On Excel 2007 Import

Mar 21, 2008

On trying to import an Excel 2007 file into Access 2007 I get the following:

1) Choose "External Data," Import, Exce
2) Select small file in MS Excel 2007 format.
3) Click on Import the source data into a new table ...
4) Choose OK

Immediately get "Microsoft Office Access has stopped working. Windows is checking for a solution to the problem..." This message never resolves, so I have to click 'Cancel'.

Then I get "Microsoft Office Access is trying to recover your information..." I have to click "Cancel" here as well and then kill MS Access in Task Manager.

Event Viewer gives following message, which is of no help:
"ID: 2, Application Name: Microsoft Office Access, Application Version: 12.0.6211.1000, Microsoft Office Version: 12.0.6215.1000. This session lasted 710 seconds with 120 seconds of active time. This session ended with a crash."

Trying to import a small Excel 2003 file leads the same result. The same happens with a CSV file.

I have uninstalled and reinstalled Office 2007 as well. I am using Vista Ultimate.

I have turned off DEP.

How can I get MS Access 2007 to import Excel?

Thanks

Ken

View 6 Replies View Related

Convert 5 Digit Serial Date Within Access

Jul 7, 2015

I am importing a table from a Clarion TPS database which stores several types of data in a "IDVAL" field. The field next to it, "LBLNUM" defines what the data type is. This creates a problem, as IDVAL is a text field, which means all data stored in it (no matter what the type) is then stored as text. This includes date fields. Since they are stored as text, they end up as 5 digit serial dates, similar to how excel handles dates (with each day after 1/1/1900 being +1).

So I have a union query which derives all the values stored in IDVAL for each unique record. My query which creates my export combines this data with several calculated fields to create my final export.

Everything looks great on this final export except for my date fields, which are all 5 digit numbers (like 77945).

I have been searching for a date function that will format this back to a standard date, however I have yet to find anything that works. Is there no way to format a 5 digit serial date back to a standard date within access by using a function? Every answer I find says to redefine the table to a date field, however I don't have this option since the field in question stores a lot more than just dates.

View 3 Replies View Related

Converting Excel Check Digit Formula To Access

Nov 5, 2014

I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access.

My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number.
My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number

View 7 Replies View Related

Access 2007 Runtime Not Visible In Access 2007

Nov 26, 2007

I installed Office Enterprise 2007. Then I installed DEveloper Exrtensions, then Access Runtime 2007. All appear to have installed OK - they appear in Program and Features.
Instructions on runtime packaging tell me that a Developer option should appear in the resulting drop-down when I click the Office button (top-left round thing) when I have a d/b open in Access. It's not there! I have un-installed and re-installed the extensions and the runtime - still nothing!
Help!
I do have Runtime 2002 (XP) on the same PC, is that relevant?

View 10 Replies View Related

If Value Is A Negative Number, Display 0

Dec 14, 2006

On my "timesheet" for my volunteer database, I have a textbox called HoursRemaining. It's control source is =[HoursRequired]-[HoursCompleted]
That works great. Occationally however, the volunteer will complete more hours than required. Instead of displaying a negative number, I would like HoursRemaining to display 0.

Can this be done?

Thanks
ScrmingWhisprs

View 3 Replies View Related

Display Record Number?

Apr 3, 2008

Is there a way to display the current "record number" on a form?
Thanks

View 3 Replies View Related

Display Number Of Records

Feb 15, 2006

Is there a way to display a number (on a Form) for the number of records in a specific table? I just want to reference how many records there are. I tried to search this topic, but couldn't find anything. Thanks.

View 10 Replies View Related

Find / Display Next Available Number?

Nov 20, 2014

I am creating a form for users to enter data. Each entry needs to have a "Event ID" associated with it and these Event IDs may be used more than once but will refer to the same Event (if it was a single entry with no duplicates, I would just use the rows primary key). How to create a query that looks at a list of existing numbers (Events already in the table) and returns the next/an unused number? I would like to create a button that says "Next available Event ID" and provides the user with a number.

View 4 Replies View Related

Display Date And Week Number??

Apr 10, 2008

hi

i would like to display the date and week number on one of my forms but have no idea about dates and how to code this.
can some one help? id appreciate it it beyond me :confused:

thanks :)

rob

View 4 Replies View Related

Display Fields Based On Number

May 14, 2005

Hi Guys and Girls,

While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:

if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this

any help or direction would be great

View 8 Replies View Related

How To Display Running Number In Form?

Jul 15, 2005

Hi,
I have many records in 1 form, is it possible for me to display running number on that form? I can display running number in report by putting textbox and set its control source to =1. Is it possible to do that in form?

any idea? thanx in advance..;)

View 4 Replies View Related

How Can I Display The Number Of Records In A Database?

Aug 27, 2003

After creating a recordset how do I display the number of records in it?

View 2 Replies View Related

Queries :: Display Negative Number As HH:MM

Jul 9, 2015

I'm converting minutes to display as HH:MM but have an issue when the number is negative. How to write this so I don't end up with a negative in front of the minutes?

Format([Variance]60,"00") & ":" & Format(([Variance] Mod 60),"00")

i.e. -193 gives me -03:-13, whereas I would like -03:13

View 14 Replies View Related

Can Office Access 2003 Runtime-Based Solutions Applies To Access 2007?

Jan 29, 2007

Hello,

I have installed Microsoft Access 2003 and Microsoft Access 2003 Developer Extensions. I can create Access application by using the Package Wizard.
But after I upgrade from Microsoft Access 2003 to Microsoft Access 2007.
I can't use the Package Wizard.

How can I do to solve this?

Thanks.

View 1 Replies View Related

Display Total Number Of Records On A Report

Oct 26, 2004

I'm sure this is a no brainer for those in the know - but I dont!
So how do I display the total number of records at the end of a report?

View 14 Replies View Related







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