Computer Support Forum

Access - Start Date and End Date in a Criteria form

Question: Access - Start Date and End Date in a Criteria form

Hi there

I know how to do add a criteria to a Query. For example in the QBE pane in design view in the criteria row I enter this statement: Between [Start Date] And [End Date]. When I open up the form that is bound to the query, a message box pops up and I enter the required dates and I get the required data that is between the dates that I specify. However, I'm now finding out that this method is now becoming a nuisance.

Is there a way that I can have 2 unbound controls - one for the start date and the other for the end date in a criteria form. This way I input the dates I want for a particular customer and the form with the required data shows up. For example, I have already developed a criteria form for customers. I have an unbound combo box control that has customer names and when I click on a particular customer name, all the data for that customer is displayed in a form. In the row source of the unbound combo box control there is a SELECT .... FROM ... statement. I also have a button next to the unbound combo box control that opens up the form for that particular customer. Now I want to add two more Unbound controls in the criteria form, one for Start Date and another for End Date.

This way I can select a customer from the customer combo Box, select the start start date from the start date combo box and the end date from the second combo box control and get the desired data for that particular customer between the specified dates. I have attempted to do this but without success. Is this possible to do?? If so can someone please tell me how to do it - BE SPECIFIC SINCE I'M NO EXPERT HERE.

Thanks

Mario

Relevance 100%
Preferred Solution: Access - Start Date and End Date in a Criteria form

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Access - Start Date and End Date in a Criteria form

Mario: What you want is called a query by form (QBF). I don't know how to do them myself, offhand, but here's the MS Technet article:
http://support.microsoft.com/?kbid=235359

2 more replies
Relevance 106.19%

I'm trying to create a query that prompts the end-user for the date fields to search.

For example, I have an Invoice table that contains a "InvoiceDate" field. I want the end-user to be able to search for invoice dates between 'date A' and 'date B'.

More or less, I need to know how to setup the "criteria" in design view of the query.
Any/all help is appriciated...

I'm using MS Access 2K
 

Answer:MS Access 2K: Date (Criteria) Query

Between [Begin Date"] and [End Date:]

It will prompt them for each value.
 

2 more replies
Relevance 106.19%

Please help. We are working on query 1 (attached database) and must add the criteria to the date field to only display dates betweeen 1 april 2009 and 30 june 2009. For the life of me I can not remember how to do this with dates!
Many thanks
Cara
 

Answer:Solved: Access - date criteria

6 more replies
Relevance 104.96%

Hello,
I'm new to Access (2000), and I'm having problems setting a criteria.

I need to be able to run a report based on a licence expiry date entered onto a form.

I need to be able to run a monthly report which will tell me who's licence is due to expire in 4 months time. Currently, I am using the following:

Between#03/06/2009#And#03/10/2009#

but I have to amend the expression to extend the date range each month, rather that it automatically "counting back" from the expiry date.

I hope this makes sense and you are able to help me!!
 

Answer:Solved: Access Help Needed - Date Criteria

Try changing it to
Between date() and date() + 121.75
 

3 more replies
Relevance 104.14%

Can anyone tell me the expression i need to type into the criteria in a query that will display the most recent date from a column within a table? thanks
 

Answer:Access 97-Criteria expression for most recent date in a table

6 more replies
Relevance 97.99%

I have a form and I want to have a combo box that will Give today’s Date 5/1/2005 as a selection.
That would be today’s date.
If I select this date it will place this Date in another field That I have formatted as a short Date.
This date must remain in this box even if I close the program.
I tried to use a query but when I go to the combo box it shows a blank cell.
It does enter today’s date in the cell I want it to but you cannot view it as with other combo boxes.
 

Answer:Access-Getting Date in a form Via Combo Box

i'm not sure how to do the date with a combobox like you mentioned above, but here is another way to do this:

Private Sub txtDateCreated_Click()

'if there is not a date in this field, then when the user clicks in the box, the current date is displayed.

If IsNull(Forms![frm1]![txtDateCreated]) Then
Forms![frm1]![txtDateCreated] = Date
End If

Put a label near the text box saying something like 'click to enter today's date'.

i like this method because instead of needing both a combobox and a text box, you only need a text box.

i am assuming that your combobox would only contain the current date. in that case, you don't need it. is that right or were you going to display other values in the combobox along with the current date?
 

2 more replies
Relevance 96.76%

Hello! I'm hoping someone out there can help me. I'm creating a basic form, it would be nice if when a record is created the day you created the record could autofill into place. Does anyone have any suggestions out there how I could do this?

Thank you,
Heather
 

Answer:MS Access Date Auto-Fill Form

You could set the default for the date field to Now().
 

2 more replies
Relevance 96.76%

Hi

I have a number of problems with a database that I am creating, one of which is how to automatically add a date to a field in a form.

This is a database that will be used by multiple users, and one of the fields is called "Date last updated". I have imported the data and locked the fields that shouldn't be changed (thanks to a reply to a previous post on here). I thought it would be straightforward just to add =NOW() to the field on the form, but that doesn't make any difference. I have also tried setting the default value of the field in the original table to =NOW() and "binding" the field in the table to that. Doesn't work either! Does anyone know how I can do this?

I also have another question for you....see my other post!!!

Thanks

Ed
 

Answer:Solved: Access 97 automatically add date to a form

Ed, in the Form's On Current Event Procedure enter the following code

Me.[Date last updated] = Now()
 

3 more replies
Relevance 95.94%

A while back you (I don't remember the exact members) helped me create a query by form in Access 2000... Thank you. Now I need to know how to create a QBF using a date range as part of the criteria.

I know how to use the date range in "Design View" of a query (ex: Between #07/01/2004# and #07/06/2004#) but I don't know how to implement that into the Form.

As an example, I would want to pull all records of "Apples" (criteria one) sold between "07/01/2004" and "07/06/2004" (criteria two).

Please help!!!
 

Answer:Access 2000: Query By Form Using A Date Range

7 more replies
Relevance 95.94%

I have a Date field in a Form. I want the Date to be appear in red when it is more than 6 months older than today's date. Where and how do I make that happen?
Thanks
 

Answer:Access 2010 Date Field in Form Macro or ()Iff ??

10 more replies
Relevance 95.94%

This question was asked by someone several years ago, but I did not find the complete answer.

I need to know how to create a QBF using a date range as part of the criteria.

I have a form to run a query. The form has a combo box to limit "approved" or "pending" “new” purchase orders (PO) (criteria one), but I would like to add a second criteria for POs created between "07/01/2009" and "07/06/2009" (criteria two).

I want the end-users to be able to select the date range (enter the start date and end date). I don't want to limit their selection.

I know how to use the date range in "Design View" of a query (ex: >=[Start Date] And <=[End Date], or,
Between #07/01/2009# and #07/06/2009#) but I don't know how to implement that into the Form.

I know I need to put something like the following in the query criteria field for PO_Created_Date:

Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]

But how to make it work in the form?

Please help!

Thanks,

Yuan
 

Answer:Access 2003: Query by Form using a date range

Yuan, welcome to the Forum.
You appear to understand the query requirement, especially the "Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]" part.
So which part don't you understand?
as long as the you put in the name of your Form in the Fromname brackets and you have 2 fields on the form called BeginningDate and Text_EndDate that Criteria will work.
 

3 more replies
Relevance 95.94%

Hi all,

I have problem with query a date range... I know how to use in "Design View" of a query (ex: Between #01/01/2004# and #05/07/2004#) but I don't know how to implement that into the Form, for eg: In Criteria: Between (Forms!FormName!From_textbox) And (Forms!FormName!To_textbox) but it just returns nothing...

Please help!

Thanks
 

More replies
Relevance 92.66%

Hello,
Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

Ok,
I'm trying to build a Time Sheet database that should record daily duties via an entry form:
"frm_Work_Hours"

This form updates an underlying table:
"Work_Hours"

The date field of the "Work_Hours" table is:
"Date_Worked"

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:
"tbl_Pay_Period"

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:
"Pay_Period"

and set ... Read more

Answer:Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Relevance 92.66%

Hello!

I have linked my calendar 11.0 to my text boxes, and then to my query criteria. Everything works, except after I click on the Calendar to choose my end date and it shows up on the text box thats linked to the query, the information from the query doesn't automatically show up on my Form in other text box I have linked for results (d*count funtion, etc).

BUT, when I go into design view, and then back to form view, the Data is Updated!

So I am very confused as to how to set it up after I input the end date to automatically update instead of hitting the design view and back to form view for it to update.

Not sure if a "refresh" or "after update" function needs to be in place? I hope I explained it correctly! Thank you!
Jess
 

More replies
Relevance 91.84%

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 91.84%

I am using the "microsoft access date/ time picker" control to pick dates. These dates like termination date of a contract will end up in the database. The problem I have is that when I do this,

rst!dateIntoForce = Me!ActiveXCtlDOForce

I end up with this in my database:

1/6/2003 5:48:43 PM

How can I extract only the date part from "1/6/2003 5:48:43 PM" so that only

1/6/2003

goes into the database
Thanks
 

Answer:Date Part only from MS Access Date picker control

Format the date before it gets entered into the db. Format([Date], "m/dd/yyyy")
 

1 more replies
Relevance 91.43%

So I have this excel spreadsheet and an example of it basically looks like this:
Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End Date question

You should have posted that in "Business Applications" here: http://forums.techguy.org/16-business-applications/
 

3 more replies
Relevance 91.43%

So I have this excel spreadsheet and an example of it basically looks like this:

Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

(Sorry, the text keeps left aligning so person 2 should actually have 25% under Mar, Apr, and May.)

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End date question

16 more replies
Relevance 88.56%

I am trying to make a pop up form with two text box controls. One is named txtBegin and the other is txtEnd. The two controls are for dates. I want my report to only show records that fall between txtBegin and txtEnd.

I am having a lot of trouble getting this done, I did try it as a parameter query set like this >=[Begin] And <=[End] but I would prefer to do it with the form.

I can open another report with this:
DoCmd.OpenReport "GRTimeSheet", acViewPreview, , _
"[EntryPerson] = '" & [txtEntryPerson] & "'" but that is working with one text string. I am working with two and using dates. The MS Northwind database has an example of what I am trying to do but I am not following the code and it requires you to have the report open first. Any ideas?

------------------
Building the Ultimate site list for PC support.
 

Answer:Date range criteria for reports

10 more replies
Relevance 88.56%

Could some one please help me with the formula for this problem?I need to count the result based on 2 criteria.The first criteria looks for a name match and the second criteria looks for a match within a set date range, eg. the month of Feb.The source data for the name match is in column B from B2:B1000.The data for the date is in column C from C2:C1000.E22 Contains the date 2/1/10E26 Contains the date 2/1/10F26 Contains the date 2/28/10This is the formula I have been trying to use but haven't be able to make work.=COUNTIFS(B2:B1000,$E2,$C$2:$C$1000,E22>=E26<=F26)I'm sure the problem has to do with E22 and the way I am trying to discribe the range but after trying numerious combinations and expressions I'm still not able to get it to solve.On a seperate sheet I was able to successfully use the following formula =IF(B3=1*(D3>=40210),AND(D3<=40237))to give a TRUE or FALSE answer. Column B was reduced down to 1 or 0 for the name match and column D was the dates.I could use this to solve my problem but I wouild wind up with an incredibly large sheet of data to cover the 12 months and the 20 names for which I need to pull data. This doesn't seem at all practical especially when it seems possible to reduce it all down to 1 line of formula if I can just get it right.Any assistance would be greatly appreciated.ThanksA.

Answer:A count on 2 criteria, 2nd is a date range

I figured it out myself=COUNTIFS($B$2:$B$1000,$E2,$C$2:$C$1000,">="&E$24,$C$2:$C$1000,"<="&E$25)

2 more replies
Relevance 88.56%

In Excel, I need to do a count based on 2 criteria. Criteria 1 looks for A, criteria 2 looks to see if A falls with in a selected date range.

Answer:A count on 2 criteria, 2nd is a date range

Thanks for letting us know what you need.If you need some help with that, feel free to ask.

4 more replies
Relevance 88.56%

Hi everyone,
First post, need help badly. What seems like it should be simple is turning out to hurt my brain. I have put together a macro in Excel (pieces gleaned from the internet) to copy rows from one worksheet that have an Ordered by date of BEFORE the date that the user inputs into another worksheet. The macro does other things too, but I will paste the pertinent part below (the problem part is in red):

Private Sub Frustrated()
Dim myCopyRow, myPasteRow As Integer
Dim CFAdate, OrderDate As Date
myCopyRow = 2
myPasteRow = 25
OrderDate = Cells(myCopyRow, 9).Value

'get date for statement
Dim Dateinput
Dateinput = InputBox("What is the date of this statement? (m/d/yyyy)")
CFAdate = Dateinput
Range("H2").Value = CFAdate

myPasteRow = 25

'Paste row with paid date condition of day or before in summary
With Worksheets("Schedule and Costs")
Do While CDate(OrderDate) < CDate(CFAdate)
If .Cells(myCopyRow, 9) = "" Then myCopyRow = myCopyRow + 1
If .Cells(myCopyRow, 3) = "" Then End
.Rows(myCopyRow).Copy
Worksheets("Construction Expenses").Paste Destination:=Worksheets("Construction Expenses").Rows(myPasteRow)
Cells(myPasteRow, 1).Activate

No matter what I try, I can not get the macro to recognize and copy only the rows with order dates earlier than the input date. If I manually put the date in the macro itself, it seems to work, but I need a way for the user to input the date and the macro to u... Read more

Answer:Solved: Help with date criteria needed

9 more replies
Relevance 88.56%

I have a sheet that I track my loan closings. I am trying to write a macro that will show me all closings for the current month we are in. Column 13 is the column that has a closing date for every loan, I don't know and can't find the code that tells excel to show only loans that are the same month as the current month we are in (I am also filtering out pre-approvals); I need to know what to put where the question marks are:This is what I have:Sub PipelineShowThisMonth()'If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd If ActiveSheet.Range("$A$5:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval" ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=13, Criteria1:="???????"End Submessage edited by mecerrato

Answer:Need help using date as criteria in vba excel sheet

First, a posting tip:Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. As for your question, my first thought was to use a Helper Column in your sheet and then filter on the Helper Column.e.g. Put =MONTH(M6) in an empty column and drag it down. I used 12, but it doesn't matter. You can even hide the column if you don't want to see it.Then your code would look like this:
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=12, Criteria1:=Month(Date)
Since the Helper Column would always contain the current month number for Dates within the current Month, those numbers would be a match for the result of Month(Date) within VBA.Then I thought about it for a second longer and decided that you can't be the first one to want to filter on the current month. I used my excellent Googling skills and found the following at:https://forums.techguy.org/threads/...
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=13, _
Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
Criteria2:="<=" & DateSerial(Year(Date), Month(Date) + 1, 1) - 1There's no need for a Helper Column when that method is used. What is it doing is looking for dates that are >= to first of this month and <= the day before the first day of next month. In other words, any date within the current month.Assuming you choose the VBA-only method, please keep the concept of a Helper Column in... Read more

5 more replies
Relevance 87.74%

I have a date column displaying all payment dates.

Is it possible for the user to query by year only, thus displaying payments only from the desired year? [DD/MM/####]

I know how to set up input queries, but am not sure how to prompt for only the year correctly. Thanks for any help!!
 

Answer:Date Column, Query Criteria only Year

6 more replies
Relevance 87.74%

I am trying to get this formula to give me a count of all rows that have a value of "1" in column "AO" and column "AH" "<>Pre-Approval" or ""<>DECL" or "<>WITH" and that have column "AV" blank OR the month number equal to this month (I am using the formula =TODAY() in Goals$S$1) even if the data is filtered out. It works fine except for the month number part, i can't seem to figure out how to tell it to count it if column "AV" is blank or equal to this month.here is the formula I am trying to use:=COUNTIFS($AO$7:$AO$999,"1",$AH$7:$AH$999,"<>Pre-Approval",$G$7:$G$999,"<>DECL",$G$7:$G$999,"<>WITH",$AV$7:$AV$999,MONTH(Goals!$S$1))

More replies
Relevance 87.74%

I need help completing this countifs formula; I need to count the amount of sales that where registered in the current month, I cannot use a helper column because I get a new report everyday and it would not be feasible for me to add the helper column to the new report everyday.How can I first convert the date to the month number so I can compare it against the current month number and add that as a criteria? The part that contains MONTH($S$1) refers to a cell where i am converting today's date to the month number. SalesSheet!$AQ$2:$AQ$999 this is the range where he sales registration date is.Formula:=COUNTIFS(ASD!AS2:AS999,A3,SalesSheet!$AQ$2:$AQ$999,MONTH($S$1))

Answer:Convert a date to month to use in COUNTIFS criteria

Adding a Helper Column can easily be accomplished with a macro. You just need an easy way to have that macro available all the time. I will now offer you that way...If you are not familiar with the personal.xlsm workbook, you should be. 1 - Open a new workbook2 - Place any commonly used macros in this workbook3 - Save the workbook as personal.xlsm in your xlstart folder3 - Hide the workbook4 - Quit Excel5 - Excel will ask you if want to save the changes to personal.xlsm (the hide)6 - Click SaveAny workbook stored in the xlstart folder will automatically open whenever you open Excel. Since personal.xlsm is hidden, you won't even know it's there. However, any macros stored in personal.xlsm will be available for use.I have a dozen or so icons on my Excel Quick Launch toolbar that have macros from my personal.xlsm workbook assigned to them. All I need to do is click an icon and the macro runs on the currently active workbook. This has saved me thousands of hours of manual work over the years.I have one macro that adds a Helper Column (nothing more than a copy of an existing column of data) and also some Conditional Formatting. This helps me keep track of which pieces of data I have updated. When I change a cell in the original data column, it no longer matches the Helper Column value and turns Yellow. After I have completed all of the required updates, I click the same Icon again and the Helper Column and Conditional Formatting rules are deleted. All you need is a macro that know... Read more

4 more replies
Relevance 84.05%

I am trying to write a function that will determine the first day of our fiscal year for whatever date is inputed into it.

Our fiscal year always ends on the friday closest to 1/31.

I'm having trouble figuring out how to deal with january dates.

Any help would be greatly appreciated.
Code:

Function FiscalYearStart(dteDate As Date)
Dim dteNextFriday As Date
Dim dteLastFriday As Date
Dim x As Integer
Dim dteJanThirtyFirst As Date

'set as january thirty first of calendar year
dteJanThirtyFirst = "1/31/" & Format(dteDate, "yyyy")

'if 1/31 is a friday
If Weekday(dteJanThirtyFirst) = 6 Then

'use 1/31 and exit function
FiscalYearStart = dteJanThirtyFirst
Exit Function

Else: End If

'set as friday after january thirty first
dteNextFriday = DateAdd("d", 8 - Weekday(dteJanThirtyFirst, vbFriday), dteJanThirtyFirst)

'determine day of week for january thirty first
x = Weekday(dteJanThirtyFirst)

'if weekday is saturday
If x = 7 Then

'subtract 1
dteLastFriday = DateAdd("d", -1, dteJanThirtyFirst)

Else

'subtract 1 and the number of the weekday
dteLastFriday = DateAdd("d", -1 - x, dteJanThirtyFirst)

End If

'if last friday is less than next friday
If DateDiff("d", dteJanThirtyFirst, dteNextFriday) > DateDiff("d", dteLastFriday, dteJanThirtyFirst) Then

'use last friday and add 1 day to get start of fiscal year
FiscalYearStart = DateAdd("d&quo... Read more

Answer:Solved: Access 2007 VBA Fiscal Year Start Date

16 more replies
Relevance 82%
Question: Date form

I updated to Quicken XP. Now I wish to revert the date form to dd/mm/yyyy, like any good Englishwoman. How do I do this?

Answer:Date form

Right click on clock bottom right, click adjust date and time ;-))

1 more replies
Relevance 81.18%

I have a travel agency. When my clients confirm their arrival I have to reserve hotel for them. For that I made hotel reservation form on MS Word and copy it to canned response on gmail.
Every time I used it. But I fill date and invoice number manually.
Now I need to add date picker for current date, arrival and departure date. Also I want to know how can automatically change it's form number.
I want again
No of people = 1,2,3etc.
No of rooms = 1,2,3,etc.
Meal basis = BB, HB,FB
Please help me to solve this problem. Also send me the way that you did it for future forms please.
Thank you
 

More replies
Relevance 81.18%

Windows repeatedly asks me to update with the little yellow exclamation mark in the tray but it always fails and then keeps asking over and over again. It's trying to update something I don't even use I think PowerPoint or something; I had the option off at one point for those programs so it wouldn't keep asking but then a message pops up saying are you sure you want to leave these off you might need them.

Any ideas on how to get past this?
 

Answer:Windows Update...date...date...date....

Have a look at this link and see if it helps.

http://support.microsoft.com/kb/910339/en-us
 

1 more replies
Relevance 80.36%

Hiya,

Looking for some help as I am really stuck.

I have a form where the user enters data and then when they hit a button an update query is performed based on the record ID. However for some reason the query is not pulling the record id through when the button is pressed.

The query runs fine when the button is clicked as Ive tested it and if I have the form open and run the query manually it pulls through the recordID and works perfectly so I don't understand why it's not working.

I have noticed however if I run the query when the form is in design mode it doesn't prompt me for the value??

It's probably something glaringly obvious but I've done this so many times and never had an issue.

Any help would be gratefully appreciated.

Thanks so much

Gill
 

Answer:Access 2003 - Query not pulling criteria from form

16 more replies
Relevance 80.36%

Hi to all! Below is the code for a function that exports froma query to a CSV file. The only problem is, I need to filter the data in the query that meets the criteria given in a form. Can anybody help me with this problem?
Thanks in advance.


Code:

Public Function ExportToCSV_D(strSource As String, _
strFilename As String, _
Optional strColumnDelimiter As String = ",", _
Optional blHeaders As Boolean) As Byte
'Exports a table or query or SQL statemtent to a text file. If a SQL is passed
'as the source, enclose it in Parenthesis.

Dim intChannel As Integer
Dim strSQL As String
Dim strCSV As String
Dim x As Integer
Dim WS_LOCATION As String
'Close any Open files
For intChannel = 1 To 511
Close #intChannel
Next intChannel

'Open a channel to communicate with your TEMP file and
intChannel = FreeFile
Open strFilename For Output Access Write As #intChannel

'Write the contents of the table to the file
'Open the source

strSQL = "SELECT WPID, EDR, MOLID, AgentId, [Account No], " & _
"Start, End, NDays, CStr(RoundDbl(Fixed)) & "".00"", " & _
"CStr(RoundDbl(Variable)) & "".00"", Absent " & _
"FROM [Cheque - 1] "


With CurrentDb.OpenRecordset(strSQL, 4) 'dbOpenSnapshot = 4

'Write the headers if appropriate
If blHeaders = True Then
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & .Fields(x).Nam... Read more

Answer:Access 97: ExportToCSV Function that accepts a criteria from a form.

You need a Where section in your SQL statement like this.
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#"
 

1 more replies
Relevance 79.54%

1. I have a query already built that doesn't have any criteria built into it, because the criteria can change with the type of information that a user is looking for.

2. Rather than have users who are not familiar with Access trying to input the criteria they need on a table into the design view of a query I would like to utilize a form.

3. I would like to find out if it is possible for a user to enter the criteria they are looking for in fields on a form and have a table open with the records that match that criteria when the user clicks submit on the form. (To me it is kind of like parameter queries. But, I just want it in a form instead having the query asking the user one question at a time.)

Thanks.
 

More replies
Relevance 79.54%

I have a subform with information drawn from a query. The main form will have multiple entries on each day. The query is based on the date entered in the form so that as new entries are made on the main form, all of the entries with the same date show up in the subform. Right now, the subform updates after the record is completed, but I would like for it to update as soon as I enter the date. How do I do that?
 

Answer:Solved: Access - Subform update as soon as criteria entered in form

In the Date Field's After Update event procedure try putting
me.subformname.Requery
where subfromname is the actual name of your sub form.
 

2 more replies
Relevance 79.54%

Hi guys

Am having pain with the definition of a referece to a control that I can use in a query:

Mainform has subform and

Subform has sub-sub form

When I'm in sub-sub form, I wish to take 4 values from it for the current record and enter them into a temporary table so I can do stuff with them.
The Main form is called frmClients
The Subform is called frmClientsProjects
The SubSub form is called frmClientsProjectSubform

I need therefore to run a query that says:

"Make a new table and put in it values "A", "B" and "C" from frmClientProjectSubform WHERE "A" = value of Control "Z" on frmClientsProjectSubform

Im using Z as the current [Forms]![frmClientsProjectSubform] record identifier.

I thought it should be [Forms]![frmClientsProjectSubform]![Z] but its throwing a parameter dialogue box up asking for [Forms]![frmClientsProjectSubform]![Z].

What might be wrong here?

Hope you can help.

Chris
 

Answer:Access 2003: Form Control Reference in a Query Criteria

16 more replies
Relevance 79.54%

Hi,I have a user form where ppl choose a date from a drop down list.But it populates the cell with a number.Is there a way to stop this from happeningThanks

Answer:Date changing to Text in a VBA User Form

Similar issue click here

10 more replies
Relevance 78.72%

I'm fairly new to Access, and I'm looking for a way to make a search form that has both text boxes and combo boxes associated with fields in my table, titled Tbl_DCR. I want to be able to select certain field values and have either a sub-form or the datasheet section of a split-form display any records that have that field value in them. So if I selected values from two different combo boxes, it would only display records that have both field values in them, and so on. I'm running Access 2007 on XP. Please help!
 

Answer:Solved: Creating a Multi-Criteria Search Form, Access 2007

16 more replies
Relevance 78.72%

If I insert a text form field into a Word document is there a way to format it as a date that automatically inserts the slashes? In other words, the user would just key in 61808 for today's date and it would appear as 6/18/08.

Thanks in advance for your help!
 

More replies
Relevance 78.72%

Hi. I have created a sheet where there are multiple users and I have added a calendar (not pop-up....still haven't figured out why it won't work for me). The calendar works fine and the linked cell too.

The thing is....the linked cell format will not change to the way I want it to...it remains as "dd/mm/yyyy". I've tried to format to the date I want using the typical date format but it always stays as "dd/mm/yyyy".

I want it to be dd-mmm-yyyy (18-Jan-2014.

Is there a VBA code that would help fix this?

Thanks.
 

Answer:Form Control Calendar date will not change its format

Johfra,

What version are you using? I was able to create a custom format with dd-mmm-yyyy by using the number format and customizing the d-mmm-yy type.

Stoneboy
 

3 more replies
Relevance 78.72%

OK, I've done the settings for the time & date. However, no matter how wide I make the Taskbar, the short form continues to be shown. In Windows 7, when the Taskbar was widened, the long form would be shown. Is there something I need to do here? Or is this YET ANOTHER BUG?

Answer:How to get the Taskbar to show the long form of the time & date?

Date and Time Format - Change in Windows 8

Try that out.

9 more replies
Relevance 78.72%

Turned on the computer today and no date. I did nothing to my computer recently but all of a sudden the "date" portion of the clock is gone!I went in the control panel, clock settings, and the short and long date formats are fine....Can anyone help me out?!?!Thanks in advanced!

Answer:Date disappeared form clock in system tray!?

Right click your Taskbar, choose Properties and make sure that it isn't set to use Small Icons. If it is uncheck it.http://www.sevenforums.com/tutorial...Tufenuf

3 more replies
Relevance 78.72%

I am trying to make a text box update to Now() whenever someone changes the data on a form using:

Private Sub Form_DataChange(ByVal Reason As Long)
Date Changed = Now()
End Sub

Or

Private Sub Form_DataChange(ByVal Reason As Long)
[Date Changed] = Now()
End Sub

Nothing seems to work. What am I doing wrong?
 

Answer:Solved: Update date when form data is changed

Been struggling with this one for hours! My boss wants to know if any of the form data was changed, I don't need to know what was changed, just that something was changed by populating a date everytime a change was made. (Probably should also include who changed it by using Environ("username"))

I have been looking for code all over the web and keep seeing that I should use the "Before Update" event but when I use this it populates the text box with a date just by simply going to the form and looking at it (Not what I need) I thought that using the "Data Changed" would work but I can't get it to do anything.

My text box for the date is "Data Changed" and the text box for who changed it is "Changed By"

Does anyone know how to do this with code, macro,???
Thanks, Joe
 

2 more replies
Relevance 77.9%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 77.9%

Help! I am using a Seagate Central 4TB NAS drive attached to my router via Ethernet. It has been working fine so far for all kinds of file activities. But whenever I modify a file in one of its folders, the creation date for the file also updates and is
changed to the same time as the new modified date.
Maybe relevant details:
I can move/copy folders to the drive and they maintain their created dates.
It happens for both a private user share on the NAS and for the Public share on it.
It happens for at least image files and text files.
I am able to edit files across two computers on the network just fine, the create date stays the same.
This happens from a new Windows 10 machine and Vista machine both.
I notice sometimes the date created seems to stay same until I access the file the next time.
I can't update the NAS firmware because of error.

If it could be a permissions problem, I have been unable to change it from:
Everyone: Special Permissions only
nobody (Unix Group\nobody): Special Permissions only
nogroup (Unix Group\nogroup): Special Permissions only
(Those were there automatically, they are not something I manually put there. I get "Access is denied" whenever try to add permissions or change ownership.)

I have Googled many phrases of this problem far and wide and come up with nothing. I really need to save the original create date because that's how I sort them in programs like Windows Photo Gallery or Picasa. Does anyone know what's goin... Read more

More replies
Relevance 77.9%

Greetings, and thanks in advance for your help..

Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year +

Now (probably starting in the past couple days, if not today), on one of those two PC's, this format will no longer display. It is Okay on the other PC.
What has changed that the taskbar clock space used to expand to fit the "short date" format, but now it will no longer expand - and instead just refuses to display the date at all. (NOTE: I've verified the issue by testing shorter formats -- short formats, like "ddd" show up correctly. Only if you request a longer format will the date not display - evidently because it is too long for the clock area. )

Additional Notes that may help:

* "Working" pc is running build 10586 PC with too small a clock space that won't expand -- Build 14393

*Other differences: Notification/Action center Icon is left of clock on the working PC - Right of clock on the non-working
Right- Clicking on task bar gives "properties" option on working PC -- But "Settings" option on the non-working

( I don't want to prejudice responses - but clearly the implication to me is that some recent OS update is most likely the culprit.)

Thank You

Answer:Taskbar Time / Date area quit expanding to fit desired date format

I had a similar problem which has been greatly ameliorated thanks to a kind respondant - see https://www.tenforums.com/installatio...-new-post.html

1 more replies
Relevance 77.9%

I got a Refurbished Dell Latitude D600 Laptop from ebay yesterday on Tuesday, September 26th 2017, It had Intel Pentium M (Non-Centrino) and Windows XP Pro SP3, but when i boot it up, it resets to The Default BIOS Date, can someone help me?

Answer:Dell Latitude D600 Current Date Resets to The Default BIOS Date

The coin cell battery on the motherboard that keeps time while the system is powered off is probably dead.  Not too surprising given that the D600 is about 15 years old at this point.  If you open the system up, it should be easy to find, so just note the type of battery, e.g. CR2032, and then buy that type of battery at any grocery/convenience store.  Should only be about $2-3.

2 more replies
Relevance 77.9%

Greetings, and thanks in advance for your help..

Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year +

Now (probably starting in the past couple days, if not today), on one of those two PC's, this format will no longer display. It is Okay on the other PC.
What has changed that the taskbar clock space used to expand to fit the "short date" format, but now it will no longer expand - and instead just refuses to display the date at all. (NOTE: I've verified the issue by testing shorter formats -- short formats, like "ddd" show up correctly. Only if you request a longer format will the date not display - evidently because it is too long for the clock area. )

Additional Notes that may help:

* "Working" pc is running build 10586 PC with too small a clock space that won't expand -- Build 14393

*Other differences: Notification/Action center Icon is left of clock on the working PC - Right of clock on the non-working
Right- Clicking on task bar gives "properties" option on working PC -- But "Settings" option on the non-working

( I don't want to prejudice responses - but clearly the implication to me is that some recent OS update is most likely the culprit.)

Thank You

More replies
Relevance 77.9%

hello to everybody

I need batch code to check if date is modified in three files and that date should be compared with current date (%date%).If so,do some job,otherwise quit (exit /b)

I found a bunch of solutions but what troubles me is date format.
On my machine it is dd.mm.yyyy

Thanks in advance.
 

Answer:Solved: batch to compare file modified date with current date

10 more replies
Relevance 77.9%

Searching for files modified in a specified date range (of the past 3 days) gets a search result list titled "date modified", and with the column heading "Date Modified" but the date listed against a file modified today is wrong. The date displayed matches the file creation date & is within the specified range ... but how do I know what the Search is doing?

Can't trust it... !

Answer:File search (by date range) displays incorrect Mod Date in results

That is very strange. Bump for a possible solution, since I can't think of one off the top of my head.

1 more replies
Relevance 77.9%

After running a large update of windows on May 18 I found that ALL my files in ALL my folders are showing a Date Modified of May 18 2013. Can't find a similar report of this problem. Any ideas?
Using Windows 7 Professional 64-bit

Answer:File Date Modified reset to today's date after windows Udate

Would this help? enter link description here

1 more replies
Relevance 77.9%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 77.9%

The title is self-explanatory. So far, I found a software that is able to change this called 'Attribute Changer' but it only changes the date modified in the folder and, when you go into the actual word application > file > info, the date created and modified are not changed. Any help would be greatly appreciated! Thank you!
 

Answer:Changing 'date created' and 'date modified' in Microsoft Word 2013

7 more replies
Relevance 77.9%

Hello.
I'd like to see Long Date and Long Time in my System Tray instead of Short Date and Short Time. 
But due to some restrictions connected with my work I can not modify Short Date & Short Time to look how I want them to look in System Tray.
So basically I need Windows to show what is set as Long Date & Long Time in System Tray instead of showing Short ones.

Is it possible? Maybe some registry tweaks?

Answer:How to display Long Date in System Tray (without modifying Short Date)?

Hi,
There is no workaround to display the Long Date in taskbar without modifying the Short Date parameter.Ivan-Liu
TechNet Community Support

8 more replies
Relevance 77.9%

Hi! Does anyone know what is the format of the date in the field "(battery) Manufacture Date" informed by Lenovo Vantage (Hardware Configuration > Energy)? It seems not to follow Windows setting. In my case, it is set to Brazilian Portuguese DD/MM/YYYY, but there is not a leading "0" in the returned value (see attached image). Does it really disregard the Windows setting and presents it in a fixed format (maybe American English)? When was my battery manufactured (Oct. 07 or July 10)?Thanks in advance.  

More replies
Relevance 77.9%

It looks like the 31 March deadline for the latest Edge promotions is the date of invoice rather than the date of order. Given Lenovo's reported long build time and slow delivery (= late invoice), I suspect that ording now from the Lenovo web site will see an invoice date outside the qualifying period. Even a dealer who says he can 'get one in' may not deliver and invoice until April or beyond. I think the only option is to find a dealer who unequivocally has current stock.Can anyone shed any light on the likely position. I do, though, wonder about trying to take advantage of Lenovo Cashback at all, given the horror stories reported here.

Answer:Edge 31 March promotion deadline - order date or invoice date?

My experience with cashback is a deathly silence from Lenovo to emails asking for progress on the cash or even recognition that there has been a registration for cashback...Currently considering giving Lenovo some publicity on the matter.Unless you have a lot of time to possibly get no where I would steer clear of the alleged cashback scheme...

1 more replies
Relevance 77.9%

I forgot my new mobile password.Also i remove battery then time setting is now incorrect.I try 5-6 type to set password

Answer:I forgoet my scree lock password?Also i remove battery ow date setting is not current date?

hard reset your phone using following steps :-
1. Turn off your Windows Phone. Use power button to power off the phone.
2. Press and hold Volume down key and connect the charger to phone. You will see a big exclamation mark on the screen.
3. Press these keys one after another in the same sequence. Volume Up, Volume Down, Power, Volume Down.
4. You will see the phone reset and boot normally. You will see wheel gears working on the screen. It will take few minutes.
Your phone will be reset to factory settings and you need to set up the phone again.
Or try to reset phone's setting if it is connected to Wi-Fi or data is enable through your account.

more replies
Relevance 77.9%

Hi !

Can somebody please help with this problem?
I have to use a textbox to enter data into a form in MS access 2010
and use that data to search for a record in a table.?
In my case this is how it is supposed to be: ,whenever the user enters a date of birth,it will search in a table for all the families (identified by a unique family code) which have their baby scheduled to be born that day.and then it will return the matching family code.THis will then direct the user to a new form where they can enter new data.
I am really confused as I am new to Visual Basic.I am to manipulate tables until AfterUpdate event for the textbox.
Any help is appreciated.

Thank you for reading!!:
 

Answer:Regarding data entry in the form of a date into a textbox and using it to search for a record.

hey Aard
am not getting it right
but you can always use ms access query with parameter

if make the problem more clear it would good
 

3 more replies
Relevance 77.49%

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

Answer:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

1 more replies
Relevance 77.49%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

Answer:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 77.49%

Hi all,

I'm new to Windows 7 (Home Premium 32-bit) and I just can't figure out what's going on here! Basically I want my short date format to be dd/mm/yyyy (UK format) e.g. 05/11/2010, but when I key a date into Excel 2002 it is appearing as 05 11 2010. In Control Panel I have the short date format set to ddd dd/MM/yyyy and the example is shown as Fri 05/11/2010, but in Excel all the date formats are shown with space separators, e.g. 14 03 2001! I don't think it's an Excel problem though as in Windows Task Scheduler the dates are shown with spaces there e.g. '19:25 on Mon 01 11 2010'. The date by the clock though is shown correctly with slash separators!

I know in Windows XP you can select the date separator from a drop-down list, but this option seems to be absent in Windows 7. Under HKEY_CURRENT_USER\Control Panel\International in the registry, sDate was set to a space, but changing that to a slash (and rebooting) didn't fix the problem.

Does anyone have any ideas please what's wrong and how to fix it?

Thanks
Steven

Answer:Date format using space as date separator rather than forward slash!

Hi,

The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


OS

9 more replies
Relevance 77.49%

Hi all,I need dos command to compare the file creation date and the modified date.My scenario is,If user inputs the folder path to the batch file, i have to provide the file list (which are under sub folders of the given folder) ,which do ot have creation date and modified date as same.can anyone help in thisThanksVanisree

Answer:Dos command to compare file creation date and modified date

You need a CMD.EXE Command Prompt solution, XP does not have MS-DOShttp://ss64.com/nt/ARM Devices are the future, great British Design !

2 more replies
Relevance 77.49%

Hi all,I wish to create a macro that when i click a button i can input what month i wish to print out data for. I have 7 columns (A-G).Date Day Activity Income 02/01/2012 Monday Work (8) £48.00 02/01/2012 HMOT £5.00 02/01/2012 Peter Taylor 134024855 HMOT £5.00 02/01/2012 Susan Taylor 134023422 HMOT £5.00 02/01/2012 Steve Davy 13402737404/01/2012 Wednesday Work (8) £48.00 04/01/2012 HMOT £5.00 04/01/2012 Tom Thompson 134027410 HMOT £5.00 04/01/2012 John Ackroyd 134025131 HMOT £5.00 04/01/2012 Aftab Ilahi 134022546Above is an example of the sheet.In column A we have the date, B we have the day, C we have the activity, D we have the income, E is a cell with the previous month date in to track data for the month, F is a name and G is a number.I wish to be able to print all of january for example, rows A-G by clicking a button and entering something like 'january' or '01/12'. This will then select all of the rows with data in based on the date range in column A and print the selection.Please feel free if that doesn't make sense to ask questions.Thanks in advance.

Answer:acro to input date and print based on date range

Below is the code I have for a macro which I can't seem to get to work right.Thanks.
Public Sub PrintMonth()
On Error GoTo Proc_Error

Dim wksCurr As Worksheet
Dim rngTarget As Range

Dim strInput As String
Dim datInput As Date
Dim datStart As Date
Dim datEnd As Date

strInput = InputBox("input start date for month", "Enter date")
If strInput > "" Then
datInput = CDate(strInput)
If datInput > 0 Then
Set wksCurr = ActiveSheet
Set rngTarget = wksCurr.Columns(4)
datStart = WorksheetFunction.EoMonth(datInput, -1) + 1
datEnd = WorksheetFunction.EoMonth(datInput, 0) + 1
With wksCurr
If .AutoFilterMode Then
.AutoFilterMode = False
End If
.UsedRange.AutoFilter Field:=rngTarget.Column, Criteria1:=">=" & datStart, _
Operator:=xlAnd, Criteria2:="<" & datEnd
.PrintOut
.AutoFilterMode = False
End With

Set rngTarget = Nothing
Set rngDate = Nothing
Set wksCurr = Nothing
End If
End If

Proc_Exit:

Exit Sub

Proc_Error:

Select Case Err
Case Else
MsgBox "Error " & CStr(Err) & ": " & Err.Description
Resume Proc_Exit
End Select

Exit Sub

End Sub

3 more replies
Relevance 77.49%

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

Answer:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

3 more replies
Relevance 77.49%

Somehow the date on one of my old Excel files has turned from 1999-10-04 (i.e. October 4, 1999) to 4617-04-11. How can I change all the rest of the incorrect dates back to what they should be? This is the only date that I can confirm. And I have no idea what the rest of all the dates should be on this spreadsheet. TIA.

Answer:Excel date problem - to convert incorrect date to original

I have no idea what the rest of all the dates should be on this spreadsheetThen how do you purpose to change them, when you don't know what year they are supposed to be?Somehow the date on one of my old Excel filesAny Macros running that may have in some way impacted the dates?How is the data stored? As Text or as a Date?MIKEhttp://www.skeptic.com/

5 more replies
Relevance 76.67%

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!
 

Answer:Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2
 

2 more replies
Relevance 76.67%

The AVG on my Dell thinks the Virus database is out of date even though it has the most current update?

How can I change the date back and what might have caused this to happen in the first place. I've heard of some AVG update problem but didn't find much relevant information on it.

Any suggestion or links would be great.

Thanks,
Andre
 

Answer:AVG Free wrong date set; Update thinks it's out-of-date

Check the date on your computer.. And, maybe uninstall AVG and reinstall it..

http://www.majorgeeks.com/AVG_Free_Edition_d886.html
 

2 more replies
Relevance 76.67%

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

Answer:Excel date auto populate date/time

11 more replies
Relevance 76.67%
Answer:ican not set date and time an error is occurewhile date and

Enter the BIOS & change the date/time. If it resets after the system has been shutdown for a while & then restarted, the CMOS battery is probably bad.

2 more replies
Relevance 76.67%

Hello, I have spreadsheet with a number of dates and times ie (11/19/07 8:57 AM) in column A and data going along with each of these dates and times from columns B-CK. I am trying to limit the list to show the information that is for all times after 2:45PM. The problem is I cannot sort by time and the dates attached to all the times span from 2004-2007. Please help!
 

Answer:Stripping the date portion of date/time in excel

7 more replies
Relevance 76.67%

The column heading in Win 10 File Explorer is 'Date'. But which one?

I've always been used in XP to the default being 'Date modified', but it seems that here it's Date created? Several re-saves have left 'Date' unchanged.

--
Terry, East Grinstead, UK

Answer:File Explorer Date column - whiich date is it?

Hi:
What is the column in Windows 7 explorer? It matches no date column from Windows XP explorer - Super User

2 more replies
Relevance 76.67%

Dell Dimensions XP PC bought 9 years ago.On opening date 7 time reverts to date of purchase.Can change and all ok.Can anyone Help Please? Thank you

Answer:Date & Time reverts to Date of PC purchase 9 Years Ago

http://www.pcadvisor.co.uk/forums/1/tech-helproom/4204823/date-amp-time-reverts-to-date-of-pc-purchase-9-years-ago/
see duplicate thread

1 more replies
Relevance 76.67%

Eg: In the below table (Excel) right now date is numeric want to convert them to DATE format Date of Birth Sex Date of diagnosis1 1/07/41 F 21.02.092 12/06/90 M 2/24/22614 1/07/62 M 11/02/095 1/07/48 F 26.05.096 1/07/39 F 11.02.097 1/07/76 F 2.05.098 1/07/60 F 30.05.099 1/07/80 F 1.03.0911 1/07/56 F 22.03.0914 1/07/65 F 22.02.0916 1/07/74 M 22.02.0917 1/07/67 F 24.03.0919 1/07/33 M 25.02.0920 1/07/45 F 21.03.09

Answer:Convert multiple date entries to DATE format

Whoever wrote the file couldn't even decide on a standard date format. Needs two routines. I prefer to normalize to yyyy/mm/dd.

7 more replies
Relevance 76.67%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 76.67%

Dell Dimensions XP PC bought 9 years ago.On opening date 7 time reverts to date of purchase.Can change and all ok.Can anyone Help Please? Thank you

Answer:Date & Time reverts to Date of PC purchase 9 Years Ago

Wait for the more knowledgeable to report but a new CMOS Battery would seem to be in order.
Easy to replace and only £1, or so, to buy, in almost any Supermarket.
About the size of a 10p piece on the motherboard, with a number like CM032. Don't take it out until you are ready to replace it.
Get the replacement > Take the old one out > Instal the new one and, with luck, that should be it.

6 more replies
Relevance 76.67%

Excel 2013 and Windows 10 Pro

Today is May-14

Date format is set to show: Mar-16
Pressing Ctrl+; shows: 05 14 2016 in the cell
and it converts to: May-16 after I exit that cell.

Any ideas as to how to fix this or why it adds 2 days to the date?

I don't do much on computer now because of health but when I do and there is a problem, I know some of the experts are here.

Thank you in advance, Ron
 

Answer:Excel Date shortcut posts proper date and then changes

Hey Ron,

This should be a fairly simple one. I'd hazard a guess and say the May-16 is the month and year rather than the month and day.
If you click the format drop down as shown below, and click the last option for more formatting options.

In the box that pops up, navigate to Custom using the left hand panel to navigate.
Here you can make your own (or use a preset if you wish) format to suit your needs.

Here is a quick key to help you get by:
dd = Day in Number format
ddd = Day in Short text
dddd = Day in Long text
mm = Month in Number format
mmm = Month in Short text
mmmm = Month in Long text
yy = year in 2 digits
yyyy = year in 4 digits

You can separate these with pretty much whatever you want.
All of this data is input into the "Type" field as shown in the picture above.

See the picture for an example, Ironically it is May 16th today so it will appear the same as your issue does, but rest assured, as long as you have those D's in there, it will display the day!

Best of luck,
Let us know how you get on,

Xsage
 

1 more replies
Relevance 76.67%

I accidentally formatted the date cell & it only accept numbers

Answer:how to correct a date field when you enter a date a number a

I don't know what you mean by "it only accepts numbers".Why can't you just format it as a date again?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.67%

I've just moved "My Documents" contents from one laptop to another
I now find that some of the Word Documents are showing the current date in letters to customers that were created & dated months ago
As these letters are records documents sent out is there a way of preventing them opening with the current date

I've already checked, Wotd - Insert - Date & Time & the Automatic Updates box is unchecked

Thanks for any help

Answer:In Word the Original Date in a letter changes to current date

Is this the file modified date or the file created date? Moving the file will give it a new "modified" date.

1 more replies
Relevance 76.67%

Hi All,
I am using LUMIA 1320 that has Lumia Cyan.
Sometimes the time and date is getting reset to a specific date, i.e. 22nd May 2014
Due to this all reminder, alarms and new messages are not coming in sync.
Is this a virus? Does it have come from an app. If that is the case then it questions Microsoft App policies...
Anyone who has encountered this issue and has done something to eradicate it let me know.

Answer:Why is my phones time and date getting reset to a specific date?

First, windows phone can never get a virus. Its the safest platform after iOS . And no, a particular app can't affect the system's time and date. I think your should try to manually set the time and date in the settings. And see if it works.

3 more replies
Relevance 76.67%

I have a file that lists a Julian date of, for instance, 37573. How do I convert that into something like 11/06/02?

Thanks in advance!
 

Answer:Julian Date--How to convert to calendar date in Excel

That's not a Julian date, Beth.

That is actually a date value that Excel provides.

That's how many days it's been since 1/1/1900.

To change it to a real date, you should be able to select that cell or entire column, hit Format-Cell, Number tab, and choose Date, and then any one of the date formats.

Julian dates are what numbered day of the year. For instance, January 3, 2002 would be 3.

Learn lots about dates and times in Excel:

http://www.cpearson.com/excel/datetime.htm

Be prepared to be there for a while.

Happy Thanksgiving!
 

2 more replies
Relevance 76.67%

Hello. I have a cell on my spread sheet which calculates an earliest date due. The cell references other cells to achieve this and looks like this '=C51+(F8*7)' where cell C51 is a date field in the format DD/MM/CCYY and contains the date 26/12/2015. And cell F8 is a number field which contains the value '4'. This value '4' is indicating a number of weeks. So the calculation (F8*7) gets me the number of days and I then add those days to the date in cell C51 to get 23/01/2016.I want this cell to output the current date if the calculation above results in a date that is in the past. Can anyone tell me if there is a simple way to achieve this? Thank you.

Answer:Excel: Substitute a past date with the current date

If I understand you correctly, this should get you what you want:=IF("your calculation"< TODAY(),TODAY(),"your calculation")However, the TODAY() will update every day to always show the current date, assuming "your calculation" results in a date that is in the past.In other words, it will not lock in the first date it calculates. It will always display "today's" date.Is that what you want to see?One other issue: As explained at the link below, the TODAY() function is "volatile". That means that is will recalculate every time the sheet calculates, even if none of the arguments have changed. A large number of volatile functions can slow a worksheet down.https://msdn.microsoft.com/en-us/li...Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is,
one whose value cannot be assumed to be the same from one
moment to the next even if none of its arguments (if it
takes any) has changed. Excel reevaluates cells that contain
volatile functions, together with all dependents, every time
that it recalculates. For this reason, too much reliance on
volatile functions can make recalculation times slow. Use them
sparingly.

The following Excel functions are volatile:

NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 76.67%

I received a .csv file from a company with a date field that looks like this: Tue Jan 03 12:12:20 PST 2012I have tried using the Data Type options to change this to a mm/dd/yyyy format but it will not apply. I tried converting it to a serial number using*mm/dd/yyyy and it will not work ~ nothing happens. I tried using a DATEVALUE formula but keep getting a #VALUE error .. I obviously don't know how to lay it out. Can anyone give me the DATEVALUE formula for this example? I am not sure what "right", "left" or "mid" represent or how to count the value placements. THANKS!!!

Answer:How do I convert a complex date to a simple date in Excel?!?

Try this:With your date in cell A1,First, format cell B1 as a DATE fieldRight click your mouse, select Format Cell, Select Number Tab,select Date, choose the date format you want.Next, in cell B1 enter the formula:=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))That should give you what your looking for.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 76.67%

I want to calculate how long a member of staff has worked for the company and therefore need to subtract their starting date from the current date.

Answer:How to subtract a date from a date ie years/months elapsed

If you use google to search:Time elapsed calculatorThere are a few online results to help. I hope they help. For the fun of it I calculated how long I have been in existence : 2,177.43 total weeks or: 15,242.00 total days or: 365,808.00 total hours or: 21,948,480.00 total minutes or: 1,316,908,800.00 total secondsThat was from the first result google gave me. Thank god no one will be able to figure out when I was born. message edited by btk1w1

9 more replies
Relevance 76.67%

ok i have a folder on my HD that contains over 60GB of data. within this folder there is many folders and files. is there a way of simply selecting this main 60GB folder and useing a prog to make all the file and folders have the same date/time that i wish?

i basicly want all the files and folders in this 60GB folder to be ...say todays date / time.

can this be done?
 

Answer:file date on a 60GB folder - setting them all to the same date? can it be done?

http://www.febooti.com/products/filetweak/

40 day fully working trial version.

Looks like it will do what you want.
 

1 more replies
Relevance 75.44%

Well AVG 7.5 is reporting that it's internal virus database is out-of date so when I update it it says that it is fully updated, but still reports that it isn't. I have no idea what is going on here so if anyoen can help me it would be great!

Thanks, David.

Answer:Avg 7.5 Reports It Is Out-of-date And When I Update It Says Its Fully Up-to-date!

I just thought of this, has AVG not released any updates since January 31st 2008? Is anyone else having this problem, I have looked on the internet an the latest updates for AVG 7.5 are January31st.

I think something is wrong here.

9 more replies
Relevance 75.44%

Hi all!
Before I ask my question I just want to let everyone know that this site has been a great help.

So, I have a TextBox that needs to accept a date and time. Simple enough. However, the data that I need to collect has to always have a date and time but when I use General Date it will accept either Date or Time or both as its designed to do so.

I have tried setting

Format as " m/d/yyyy h:nn:ss ampm "
Input Mask as " 90/90/0000\ 99:00:00\ >LL;0;_ "

and tinkered with me.textbox.format() as an if statment

But this leads to the other issue that most of this data will be pasted into the form in either of the below formats.

7/19/2017 12:08:14 PM
Wednesday, July 19, 2017 12:08:14 PM

I'm stumped and haven't been able to figure this out through research or my own luck.

Any help is appreciated and thank you all.
 

Answer:General Date Forced To Have Date And Time

So.... I think I figured it out. Been testing it and it seems to result in the way i need it to. So I have the Form/Table set to Date/Time Field formatted as a General Date. This will at least validate that the information inputted in the text-box is a date/time of some sort. Then I added the below code to make it follow a validation rule of sort. So far it seems regardless of the way information is pasted in the end result is always MM/DD/YYYY HH:MM:SS AMPM
If Me.YourTextBox Like "**/**/**** **:**:** **" Then
Cancel = False
Else
MsgBox ("Please enter a date and time in this format MM/DD/YYYY HH:MM:SS AM/PM")
Cancel = True
End If
I'm still open to suggestions if there is a more efficient way of doing this. If not I hope this information will be found helpful to others.
Thanks All,
 

5 more replies
Relevance 75.44%

When viewing the software downloads page, all of the update dates are newer than the release dates.  And they are all very close to current date (Jun 20).  Running the HP tool doesn't show any update is needed.  So I'm puzzled. Are all these drivers/patches really out of date or is the earlier release date still the correct one.  Surprised that every available update shows close to current date.    

Answer:Update Date does not Match Release Date

Hi: The date you see next to the actual driver exe file is the correct driver date. The 'update' date you see is because HP redesigned the website and had to upload many of the drivers again.

1 more replies
Relevance 75.44%

Does anyone know how to convert a date entry from Unix format to Gregorian calendar format?

I have a long table with Unix values for date and want to plot a count of row entries within a certain date range...

Any ideas?
 

Answer:Excel: Unix date to gregorian date

Also... how do I count the number of occurrences of similar entries in a column... i.e.

1
1
1
2
2
3
3
3
3
4
4

I want to be able to count the number of occurrences of 1, then the number of occurrences of 2 etc etc
 

2 more replies
Relevance 75.44%

In excel I am trying to compare a column of various dates against the current date (reported in one cell using the =today() function) and if the dates in the column is past today's date it will report "Late" in the adjacent row or if the date in the column is the same as the current date it will report "Due" in the adjacent row. "If/then" doesnt seem to work. I have thousands of columns of dates but simplied exampleCurrent Date = 04/21/2014Column A, Column B, Due Date, Status, 04/12/2014, Late04/21/2014, Due

Answer:Comparing a date in column against today's date

First, a posting tip...If you click on the blue line at the bottom of this post, you can read the instructions on how to post data in this forum so that the columns line up and your data is easier for us to read. It may take some trial and error via the Preview screen, as explained in the instructions, but it can be done. Second, you told us what you want if the date to be checked has past or is the same as TODAY(), but you didn't tell us what you want if the date is later than TODAY().I assumed you want a blank cell.

A B
1 =TODAY()
2 Due Date Status
3 4/12/2014 =IF(A3<$A$1,"Late",IF(A3=$A$1,"Due",""))
4 4/21/2014 =IF(A4<$A$1,"Late",IF(A4=$A$1,"Due","")) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 75.44%

All --

Please help with this question about file data created VS date modifed in Windows 7.

Suppose a file is created at time1.

How come Windows7 reports "date created" as "time1" and "date modified" as "time2" where "time2" is a time earlier than time1?

See the attached file, "WindowsDateIssue201203061503.png", for an example.

Thanks.

-- Mark Kamoski
 

Answer:date-created VS date-modified on Windows 7

Good question, but it does not effect the performance of the computer.
An explanation could be the DLL file could be updated via a recent Windows update and it was last modified date was inherited from the previous file that has been overwritten.
 

2 more replies