Urgent Help Required For A Date Conversion Problem

Mar 15, 2006

I have received a Access97 database which has a date field filled with numbers.
The date of birth field is in the format : 19970131
And the date of birth field is a text field.
The software requires the date to be in dd/mm/yyyy order and also to be a date/time field.
When I try to change the text to date/time, Access deletes all dates of birth.
I am not sure how to solve this as I am very new to databases.
Can someone please help me soon?

View Replies


ADVERTISEMENT

Urgent Dmax Help Required

Jun 4, 2006

Hello gentlemen,

My main form contains a Entry_No (Text – Not duplicate) field. I used following code in the same form in a command button’s OnClick event to increment integer value of Entry_No to “Issue-1”, Issue-2”, “Issue-3” & so on.

Dim strmax As String
strmax = DMax("[Entry_No]", "T_Drug_Receiv_Head")
Me!Entry_No = "Issue-" & Right(strmax, _
Len(strmax) - _
InStr(1, strmax, "-")) + 1

No problem with above code. It works fine.

The record source of the main form initially was based on query which I removed later. I placed following code to display only the last record while form opens (on Open event). This is a try due to very large table and I don’t want my form / query to load all 90,000 records into the memory at one time that takes time.
I placed a unbound text box (TxtMaxt) to disiplay Entry_No field of last record of the table which is ok.

Txtmax = DMax("[Entry_No]", "T_Drug_Receiv_Head") ‘ This is OK.

Dim NSSQL As String

NSSQL = "Select * from T_Drug_Receiv_Head where [Entry_No] = Txtmax"
Me.Form.RecordSource = NSSQL
Me.Refresh

When I open the form, it asks me ‘Enter parameter value’ for ‘Issue’. When enter something then dialog closes and form appears with blank record except showing displaying Entry_No field of last record of the table in the Txtmax unbound text box.

When I removes code from on open event and selects query that was set before as record set, it works.

Where might have gone wrong?

With kind regards,
Ashfaque

View 12 Replies View Related

Urgent Assistance Required - **Beginner**

Jul 21, 2006

Hopefully someone can assist me. For an advanced user, this will probably take only few minutes.

We have conducted a survey online and now need to analyse the data. As the online questionaire is about 25 pages long and about 20 Managers were asked to fill out. Each manager had to describe the different positions they maintain for example, Business Analyst, Business Consultant, Postion description ..... Now the results are trickling in, its pretty tedious to go through each response one by one and compare how many common job functions there are....

It has been suggested to build a dB in MSAccess with the "questions" that were asked and upload all the responses from the online questionaire to the dB. The online questionaire & responses from each individual has been converted to excel flat files.

Can some one assist me in building the dB with the ability to upload the excel flat files one by one.

The point of this exercise is so that the analysis of the result process is not cumbersome.

Hopefully I am making sense?

View 1 Replies View Related

Urgent Required Field Advice Needed

Jul 1, 2005

I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):

I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".

What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.

Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.

Many Thanks,
Photoguy

View 9 Replies View Related

Date Conversion

Jun 10, 2005

I have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?

View 1 Replies View Related

Date Conversion

Nov 18, 2005

I'm imported data from a csv file. The dates stored in there are in a dd-mm-yyyy time format. How can I work with this since Access's date format is just mm/dd/yyyy? I imagine I need to do some sort of conversion? Does Access provide anything for me to do this?

View 6 Replies View Related

Date Conversion

Jun 10, 2005

I have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?

View 2 Replies View Related

Date Conversion

Jan 12, 2007

I have a time stamp field from an Oracle database that I want to convert to a regular date field in my Access query so that I can pull data from the table base on start and end date.
The time stamp field is formatted as: 09/19/2006 03:16:00 PM

In my query I have tried the following formatting:

1. DateRcvd: Format((Left([time_stamp],10)),"mm/dd/yy")
or
2. DateRcvd: Format([time_stamp],"Short Date")

Without criteria I get all the records in the following format:

1. 09/19/06
2. 9/19/06

Dates in the table are from 06/01/06 to current date 07.

Using the following criteria - I get varing results but never what I want. For example, using format #1, if I enter 09/16/06 and 01/10/07 I get everything for 07 and nothing for 06
If I use format #2, I get everything for 9/16/06 (no other records) and everything for 07

Criteria:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]


Any suggestions?:confused:

View 5 Replies View Related

Number To Date Conversion

Jul 22, 2006

My challenge is to fix a broken report that results from a query. The query is suppose to bring up all records within a date range. The problem is that the table was designed with the date field being text. Dates such as 06/12/2006 are entered without the beginning 0, so simple queries do not work, and the data is simply 6122006. I think the data needs converted to date first, possibly by extracting and converting. I do not know how to do this because of the missing digit inconsistency. The table cannot be changed directly to a date field without data loss.

Thanks for any suggestions.

View 3 Replies View Related

Another String To Date Conversion Problem

Oct 6, 2006

I have a table with a text field to receive dates or "na" or "n/a". From this, I am trying to run a query that selects all dates that are not "na" and not "n/a" and not null. This works fine with a criteria of

"Is Not Null And Not In ("NA","N/A")"

The problem is, I also must select a date range. In the query, I have created a calculated field equal to various date conversion functions and they all seem to work at converting the text to date. However, I get a Type Mismatch error any time I try to do anything to the date field such as select a date range or to sort on it.

Any suggestions?

View 4 Replies View Related

Unwanted Conversion Of Date Field

Jul 21, 2005

I have an Excel file that I want to import into an Access db table. In that Excel file is a date field formatted mm/yy. When I import that file, Access converts the date to m/d/yyyy. Is there anyway to re-format that data after it's imported into Access (like a global change?)? I even tried setting up an input mask for that date field, but that only seems to work when you are actually typing data into the field.

Thanks for your help!

rbgolfn

View 2 Replies View Related

Date Conversion To Weekday And Weekends

Mar 12, 2008

Hi all.

I can complete this in excel no problem with monday through sunday being 1 -7, but is the same possible in access.

i.e. 12/03/08 = Wednesday.

Many Thanks Dean:confused:

Update:

DoW: Weekday([Calldate],0)

View 2 Replies View Related

Queries :: Text Conversion To Date

Aug 11, 2015

I am the junior of the ms access.

I try to convert the below text to date

(03/08/2015 19:42)
(31/07/2015 12:20)

CDate([XXXX])
return 3/8/2015 7:42:00 PM (should be 03Aug2015)
return 7/31/2015 12:20:00 PM (should be 31Jul2015)

View 4 Replies View Related

General :: VBA Date Conversion To SAP Formats?

Oct 13, 2013

I've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .

a simple string conversion like

Code:

pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")

is not working, the year is converted wrong.

thus 17-07-62 should be converted to 17.7.1962 ( European date format )

View 1 Replies View Related

Queries :: Date Conversion Comparison Not Working?

Nov 8, 2013

I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.

The query is supposed to pull all data where the "Date Overdue" field is less than today.

"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.

It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.

"Date Overdue" is set to this value:

Code:

DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
"Date Overdue" has the criteria "<DateValue(CDate(Now()))"

I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..

The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.

I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:

"Syntax error (missing operator) in query expression 'DATE OVERDUE' "

NOTE: I'd like to add that this is just a regular Select query.

Code:
SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))
AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],

[Code] .....

View 1 Replies View Related

General :: Date Conversion From Text Value (5 Characters)

Mar 1, 2013

I have a date value in text format that is 5 character and want to convert it to a proper date format. Here is a sample of the data:

07301 actually represents 7/30/2011. How to actually convert that value to the date format of mm/dd/yyyy?

View 7 Replies View Related

Urgent: Show Last Date

Mar 26, 2007

Hi, can anyone tell me what to put to show the last date in a query?
I have a field with a few sdates in it, how would I show the latest one?


Thanks

View 4 Replies View Related

Date Query Question - URGENT

Mar 29, 2005

How can I get the record with a date field that is the earliest and the latest in a table.

tblSample(ID, Name, Type, ItemDate)

The ItemDate can be any date entered by the user, so the ID will not give me the earliest and the latest record. How do I make a query that will give me the earliest ItemDate and the latest ItemDate. I need to do this in Access. Thank you.

View 1 Replies View Related

Urgent Help Needed In Formatting A Date??

Nov 16, 2005

I have a column called date, right now it is in m/dd/yyyy format and i want to convert this to dd-mmm-yyyy format how do i do this? thx for any help

View 1 Replies View Related

Date Required After A Choice

May 8, 2006

I have a form which deals with complaints against employees.

One of the selections is a yes/no checkbox stating "No Merit" indicating the complaint has no merit.

If this checkbox is checked for Yes, I wish to require that data (an explanation) be entered as to why in your opinion the complaint has no merit.

This data would be entered in a text field called Comments.

On the "Close Form" command button I have tried to place the coding
If [nomerit]=yes And [comments]=" " then
run the message box.

I can't seem to get it to work. Any ideas gratefully received.

View 2 Replies View Related

Modules & VBA :: SQL Server - Conversion Failed When Converting Date And / Or Time From Character String

Feb 13, 2014

I have two table

1. dbo.period (OpeningDate, ClosingDate)
2. dbo.data (blah blah, doc_date)

I want to create a view as follows

Select doc_date from dbo.data
where doc_date> 'select OpeningDate from dbo.period'

both doc_date and opening date have the same format

but the error will still appear as follows:
"Conversion failed when converting date and / or time from character string."

View 3 Replies View Related

Only The Month Required From The Date Field

Nov 8, 2005

Hello everyone

I am wanting to pull out only the month part from a date field, 11/10/2005.

In the query I have made an expression to pull the month, eg 10 for October.

I need to convert this number of the month to the Title of the Month.

I made a combo box on the report based on the Expression and in the row source put

1,"January", 2,"February",3, "March", 4,"Apri", 5,"May", 6,"June", 7,"July", 8,"August", 9, "September", 10, "October", 11, "November", 12, "December"

Unfortunately it still comes out with the number of the month.

Can anyone tell me please where I am going wrong.

Thank You in advance.

View 2 Replies View Related

Help Required Again Pls(date/time Field Related)

Apr 2, 2005

Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???

View 2 Replies View Related

Forms :: Fields Are Required To Be Filled Out Even Though Not Set As Required

Nov 3, 2014

I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?

View 3 Replies View Related

Conversion To SQL

Nov 3, 2005

I'm in the process of migrating my Access database to SQL, but am encountering problems. Firstly when I exported it I discovered that all of my Access queries had been converted to Tables. After deleting these, I attempted to cut and past the SQL code from the Access Queries to SQL's "views", which I had been taught were effectively the same thing.

However, now I come to test them I get an error that says it cannot be displayed because the query is a "view object".

What is happening here, am i doing the correct thing?

Thanks.

View 2 Replies View Related

PDF To JPG Conversion

Sep 1, 2014

I have thousands of PDFs of which I want to present a number as thumbnails on a form and allow the users to select any one and have the full PDF displayed.

The only way in which I can see this working is to have the thumbnail as a JPG image and set the On Click property to display the relevant PDF. This part is quite simple, the problem I have is converting the existing PDFs to JPGs.

Any way of converting PDFs to JPGs using VBA code?

View 6 Replies View Related







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