How To Extract Date From Personal ID Number

Oct 4, 2006

Hello,

I would appreciate if someone could help on this problem.
What I want to do is to add a field in a form that would automatically show the date of person's birth after entering the persons ID. For example, I enter 39101012345. The first number (3) shows only the gender so I don't need it. The next 6 numbers show the date of persons birth - it is 910101 and it means that the birthdate is 1991.01.01
I need this field in a date format because later I will have to do some calculations concerned with the birthdate of that person.

Thanks in advance!

p.s. beer for help is quaranteed! :)

View Replies


ADVERTISEMENT

Modules & VBA :: Unable To Extract Day Number From Date Field And Write It To Day Field

Jan 10, 2014

I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.

I want to extract the day number from the Date field, and write it to the Day field.

I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".

So far, my code looks as follows:

Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()

[Code]...

I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.

View 12 Replies View Related

Extract A Number From A String?

Sep 16, 2013

I do have a module to extract a number from a string :

Public Function ExtractedValue(strSent) As Double
If IsNull(strSent) Then
Exit Function
End If
Dim strTemp As String
Dim intLoop As Integer

[code]....

My problem is that the string I do have is like:

19.0 EA
195 PAC
42 M
150 L

the problem is that when the number is like 19.0 EA the value I get is 190, in reality it should be 19. The rest is fine as there are no dot's.

View 3 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related

Query To Extract Greatest Number

Aug 14, 2007

I have a table that keeps track of how many times an account is listed. I want to write a query that will only return the greatest number of occurences for that account. For example if acct 12345 has 3 occurences in the database I only want to return the information that is associated with the #3 occurence. As stated there is a field that calculates the number of occurences for each account so in this case there would be a record for 1, 2, and 3 occurence so I only want to see #3. Hope that makes sense. Thanks!

View 3 Replies View Related

General :: How To Extract Number From Memo Field

Jun 17, 2014

for example how can I extract the following number from the field ITEM 117145 5410076462162, 714774 117072 5410076462223 ,714768 117153 5410076462285,733289 998214 5410076462193 from this record. The field is memo. The common: the string "+" always is in front I tried this Expr2: InStr([ITEM],"+"), but it only give me first one.

The following is record
BARCODE Retail Line Code NSL Code Prod Ean Name Size Facings Capacity Pos Comments Offer Description Offer Type Offer No 5ELKAHQ*agiaji+ 714766 117145 5410076462162 NS PRNGLS SUR CRM AND ON 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiagh+ 714774 117072 5410076462223 PRNGLS ORIG 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiddd+ 714768 117153 5410076462285 PRNGLS SLT/VNGR 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agibia+ 733289 998214 5410076462193 PRNGLS BBQ 190G 3F 15 BUY ONE GET ONE FREE MV 105574

View 6 Replies View Related

Queries :: Extract Or Calculate Number Of Records In A Sequence

Jan 4, 2014

How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?

View 3 Replies View Related

Extract Date

Aug 22, 2006

Hi,

If I have a date that is formatted like this 01/01/2006, however do I write a query that shows the date as follows Jan 2006.

Thanks

Regards

View 6 Replies View Related

Extract Date From Filename

Apr 12, 2008

I think I am on the right track, but I am stuck with importing the date from my excel filename. Currently the following code imports all Excel files from the directory into a table in my database. The excel files are saved like "ABC_BNG_GTR_04012008.XLS" The numbers represent the date. I need for the date to be extracted and place in table into the "callDate" field. When I run my code, I continue to get a runtime error 13 - type mismatch. The code stops at the Mid() statement. What am I doing incorrectly? Thanks in advance.

Option Compare Database

Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim TheDate As Date


DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData"

'Loop through the folder & build file list
strFile = Dir(path & "*.xls")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sheet2", filename, False

'Inserts date in date column based on file name

TheDate = Mid(strFile, 12, 8)

CurrentDb.Execute "UPDATE sheet2 SET callDate =" & "'" & TheDate & "' where callDate is null"


Next intFile

DoCmd.SetWarnings True

End Sub

View 14 Replies View Related

Need To Extract Month From A Date

Apr 22, 2008

Hi - I need to extract the month from a date via an SQL pass through query. e.g. if the date reads 19/02/2008, I need to have returned to me 'FEB', so that I can link the month (string) to another table.

I have though about "decoding" the date - I've seen that practice used in other scenarios, but never on a date format. Any suggestions?
Thanks

View 1 Replies View Related

Extract Date From Timestamp

Apr 23, 2008

I have a timestamp field in a database and only want to display the Date. In the query I placed this: Date Received: Format([RItemTimeStamp],"Short Date")

Works fine however if I try to run a query to serach for a date range it doesn't give me all the days. Is there a better way how to truncate the Date of a timestamp so that I can sort it correctly?

View 1 Replies View Related

How To Extract Month & Year Together From A Date

Apr 19, 2007

Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.

Any idea ...?

thanks

View 5 Replies View Related

Queries :: Extract Max Date And Time - How To Get Corresponding ID

Jan 2, 2014

I have a (simplified) table with

ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%

I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)

The way to extract the maximum date + time is not difficult

SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;

But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.

View 5 Replies View Related

Queries :: Extract Day From The Date And Return A Value

Feb 20, 2014

I have a list of dates on which tasks were performed.

I want to be able to see if there is a pattern, i.e. lots done on a Monday

Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??

View 3 Replies View Related

Help - Need To Extract Year From A Short Date Field

Oct 13, 2006

Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.

Ta

Humph

View 2 Replies View Related

Help - Need To Extract Year From A Short Date Field

Oct 13, 2006

Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.

Ta

Humph

View 2 Replies View Related

Tip: Personal Information

Oct 10, 2007

Did you know that when you create an MS Access database, personal information such as your user name and the name of the person or company that the software is registered to is saved and displayed with the database file for others to see. Prying eyes simply goto 'File->Database Properties->Summary Tab' to see your personal information.

The fix is simple goto:'Tools->Options->General' and select the 'Remove PersonalInformation from file properties on save' check box. Next time you save, no more personal information.

Don't believe it? Look at some of the sample databases posted here - :eek:

:)
ken

View 1 Replies View Related

Queries :: Extract Day Of Week From A Date To Link To Data

Dec 8, 2014

I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.

I then want to link this "ddd" to data in my table that contains the three letter days of the week.

How do I make the day become "static" in order to be able to link.

View 6 Replies View Related

Insert Personal Sugnature In A Form

Feb 14, 2005

Hi all.
I have the following problem. I have several users which can fill in a quatation form.
Every user select his name from a combo box.
What i would like to do is that automatically when a user select his name to add
at the end of the quotation his personal sugnature which is scanned.
But how u can insert it to the Database? What is the easiest way?
Any idea?
Thank you

View 1 Replies View Related

Calculated Field To Extract 14 Day Period And Check If Today Date Falls Within?

Dec 3, 2012

I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.

Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:

PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")

i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December.they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)

View 3 Replies View Related

General :: Personal Expense Ledger - Adding Values In A Listbox

Mar 5, 2013

I am working on a personal expense ledger. On a particular field that is labeled [expense/income], and a field labeled [amount]. the [expense/income]field is a listbox field "with expense and income as the values. Here is what i want to do. when i select income from the listbox i want it to sum the total in the amount] field, and if i select expense i want it to subtract the total in the [amount] field. i hope i explain it correctly.

View 4 Replies View Related

General :: Auto Email Personal Reports To Multiple People Automatically

Nov 21, 2013

I have put together some Access 2010 Databases and I am about to embark on another soon. I have been given a request that I can't seem to solve yet so I am goint to ask it here as a good starting point. The goal is to build a database for a magazine. They want to be able to run a report to generate slips for subscribers whose subscriptions have expired. I would like to, if possible, generate reports and send them to the respective clients via email, all with the push of 1 button. I don't want to have to find each subscriber and do one off reports.

View 1 Replies View Related

Prompt User To Enter Personal Password When Ticking Option Button

Mar 24, 2015

I have a form in which I collect approvals from two different departments. To approve an item the user selects their name from a combobox, and then tick an option box to indicate approval. The combobox is from table 'Users' and has a query as a source with the following fields, 'UserNum, First, Last, Password'. The 'approval' fields are on table 'Approvals' and are yes/no fields.

What I'd like to have happen is that the user chooses their name from the combobox and then ticks the option box for approval; when ticked I would like a messagebox to appear asking for the users password based on the name chosen in the combobox.

Is this even possible?

View 6 Replies View Related

Calculate Final Date Based On Begin Date And Number Of Days

Jun 30, 2015

I need to calculate the final date based on the begin date and the number of days.

The name of the fields are: sdatainicio; diasatribuidos; sdatafim.

View 7 Replies View Related

Calculate Number Of Days Between Date Job Is Open And Todays Date

Jan 28, 2013

What's the formula we can use to calculate days between no of days between date job is open and today's date in a query. I am using formulat below formula but the DATE_OPEN and Today (todays date) pops us as enter parameter value. I dont want those enter parameter windows pops us. The date job is open is in a linked table and linked table is getting the information from BPCS software. The date in Liinked is in format YYYYMMDD.

Total Days: DateDiff("d",[Date_Open],[Today])

The SQL Details are below. AUNPRDF_AAA802 is a linked table and information gets updated on 12 am everyday

SELECT DateDiff("d",[Date_Open],[Today]) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AAWKSP AS Workshop,
AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint,

[Code] ....

View 7 Replies View Related

Adding A Number To A Date [Can Date Cope]

Sep 17, 2007

Here's what I currently have:

- A table with a number of fields about a course
- A course commence date
- A number of days the course will take field
- No course end date

Is there a way to easy add one field to the other to:
Give end date i.e. do I need to write the code to validate all dates which bring the date over a month e.g. start date 29/sep/2007 and number of days 5.

Does date have the functionality already?

Thanks in advance

-Elfman

View 2 Replies View Related







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