Modules & VBA :: Convert Weeks To First Day Of Month
Feb 28, 2014
I am trying to convert a week which is entered in a text box on a form to the first day of the month in which it falls.
My text box (txtdw) is in the format yyww and I want it to be converted to 01/mm/yyyy.
example: 1401 would convert to 01/01/2014. 1406 would convert to 01/02/2014.
I am pretty close, I can get to the correct month but I can't work out how to change to the first day of the month.
Code:
DateAdd("D", Right(Me.txtDW, 2) * 7, DateSerial("20" & Left(Me.txtDW, 2), 1, 1))
View Replies
ADVERTISEMENT
Oct 13, 2014
I have a table that has just Months in a column. As in January, February......
I want to convert these to Numbers as in 1-12 in VBA. I tried all the DatePart in every combination but can't seem to get the results.
View 5 Replies
View Related
Jun 2, 2006
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
=Int(DateDiff("d", Date1, Date2) / 7)
but this does not work for the 5 week months?
Any help would be greatly appreciated.
View 5 Replies
View Related
May 31, 2007
Hi
Would be great if I could get some help on this:
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
Any help on this would be much appreciated.:D
Thanks
Daniel
View 6 Replies
View Related
Apr 9, 2008
Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:
MonthName(Month([CompletionDate])) = [Enter Month]
but
MonthName(Month([CompletionDate])) <= [Enter Month]
does not work.
Thanks!
View 1 Replies
View Related
Feb 4, 2008
Hi
I am struggling to get a number to change to a month name.
When the user chooses a month from a drop-down:
Value List
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"
I use the month number to run the query, ie: 1, 2, 3 etc.,
But I want the Month Name to use in the subsequent report.
I have no other data in the query other than a month number to use so I thought about an invisible text box that get's the month number and I could reference it from the report, but I just can't get the text box to get the month name from the drop-down.
=Month([Month4Query].[Column](2))
Maybe there is a better, easier way?
Martin
View 3 Replies
View Related
Sep 29, 2005
Help!!
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??
View 1 Replies
View Related
Jul 18, 2014
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.
Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
View 1 Replies
View Related
Aug 17, 2015
I have 2 columns that are listed as such:
AssumptionMo AssumptionYr
MAY 2014
JUN 2015
JUL 2015
OCT 2016
I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month. ex May 2014 needs to read 5/1/2014. When I use the expression AssumpDate: DateValue("1-" & [Assumption_Month] & "-" & Year(Date())) of course the year changes to the current one--2015. How can I I change the expression so that the year is based on the AssumptionYr column?
View 3 Replies
View Related
Mar 11, 2014
I have a textbox in a form. What I want it to do is to display the following weeks Monday date. Sounds simple but requires you to know the Monday dates in advance ...
View 14 Replies
View Related
Dec 15, 2013
Everything I have goggled and tried doesnt work How do I convert the where condition to VBA
Code:
Form name frmMap
View form
Filter name
Where condition [chid]=[forms]![frmCurchesAll]![chid]
View 12 Replies
View Related
Jun 28, 2013
I have the following code for hours worked by staff over a 24hr period. However, I want to calculate staff pay by using their hours worked. The table I am using is populated with the minutes i.e. 270 but I want this field to display 3.5 hrs. I have initially got round this by using a calculated field in the table [Hoursworked]/60*[StaffPay] but this is not an ideal fix. Can I add something to the code below to display the hours to 2 decimal places.
Code
Dim ilHoursWorked
ilHoursWorked = IIf(CDate(Me.txtStartTime) < CDate(Me.txtFinishTime), DateDiff("n", CDate(Me.txtStartTime), CDate(Me.txtFinishTime)), 1440 - DateDiff("n", CDate(Me.txtFinishTime), CDate(Me.txtStartTime)))
View 2 Replies
View Related
Jul 12, 2015
We have a ton of Access 97 DBs I've been asked to "fix". Since moving to Windows 7 some of them have been giving Error70 comands, which I've narrowed down to Sendkey not playing nicely with Windows 7. Anyway, I was able to fix it for one user by turning UAC off. Not my preferred way to operate, but it's a stopgap until I can get this mess sorted out.
Ok, here's the code. This is an event procedure that is tied to a date field where staff are to enter a date in MM/DD/YYYY format. For some reason there is no tab order anywhere on this DB so they have to click to enter. In the past they had to make sure that they clicked at the left most M in MM to make the entry.
It appears that this Sendkey is the culprit here. Is there anyway I can safely remove that w/o breaking something? I cannot get sole access to this DB as it's in use pretty much 24/7 until the 11pm backup for the server it resides on. It is Access 97. I was thinking of just pulling the macro from that field but then again I am not an Access person by any means,
Option Compare Database
Option Explicit
Private Sub cboxptno_GotFocus()
cboxptno.Dropdown
End Sub
----
Private Sub Command111_Click()
On Error GoTo Err_Command111_Click
[code]....
View 10 Replies
View Related
Jul 1, 2015
I have the following script that either converts a single linked table to a local table or ALL linked tables to local tables, however I am getting the following 2 error messages on several tables for the ALL tables script.
error 3709, the search key was not found in any record
Error 3300, cannot create a relationship.
Code:
Sub Convert_Linked_Tables_To_Local()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
GoTo Multi1
[code]....
View 5 Replies
View Related
Aug 20, 2013
Creating a recordset from an Access table
Copy that recordset into a Variant variable, similar to a matrix
Run a Heuristic Algorithm on the matrix to populate values
Delete * from the orginal Access table
Input new data into the Original Access table by looping through matrix with an INSERT INTO statement.
This method works but I do not like the loop in step 5 as I am calling an INSERT INTO statement 800+ times and can be slow. Any way to view the variant as a whole and not have to loop through the entire matrix. Maybe possibly converting the variant to an Access tabledef.
View 2 Replies
View Related
Apr 7, 2014
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
View 5 Replies
View Related
Feb 17, 2014
I have a problem in converting the content of a simple table to a text format I need for an other program. The table is containing 3 columns
- ITEMID
- COLOR
- MINQTY
How do I convert the table to a text format with below format.?
ITEMTYPE is all ways >P<
How is this text shown. Do I make a kind of report.?
I have attached the two document (Text format and DB)
<INVENTORY>
<ITEM>
<ITEMTYPE>P</ITEMTYPE>
<ITEMID>3622</ITEMID>
<COLOR>11</COLOR>
<MINQTY>20</MINQTY>
[Code] .....
View 1 Replies
View Related
Sep 9, 2013
I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"
To elaborate: CB1 contains the following values
Code : elephant, giraffe, bufffalo, tiger, lion
Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals
Code : **SELECT elephant, tiger FROM dbo_animals**
Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as
tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)
It looks like
Code : SELECT Elephant from dbo_animals
This is how I want. But, since i am looping through CB controls, I have it set up as
Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"
and this shows up as
Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals
Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.
This makes sense, since it is unable to evaluate the text as a reference.
How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?
View 4 Replies
View Related
Aug 7, 2015
I have files that have extension of TSV which are text files but viewable in exel. I figured out a way for the user to click on a button in Access which does the following
1. Run Macro in Excel: The macro prompts the user to select the TSV file. After selection, macro opens the employee.tsv file in the excel (with excel being invisible) and saves it as employee.xls
Code:
Sub SaveTSVtoXLS()
Dim myPath As String
Dim myString As Variant
Application.DisplayAlerts = False
With Application.FileDialog(msoFileDialogOpen)
[Code] .....
2. Imports the Excel file (employee.xls) into two tables: tblEmployee and tblDepartment using the following codes.
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryDepartment", selectFile, True - 1, "A1:C2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryEmployee", selectFile, True - 1, "A1:AE2"
Everything is working flawless except that the user has to select the file three times:
1 time for the tsv
2 times for the xls file
Is there a way that the user can select the file only once (tsv file) or at least only twice one of the tsv file and the other for the xls file?
View 8 Replies
View Related
Jan 31, 2014
I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.
View 7 Replies
View Related
Jan 7, 2014
In the attached Database (Access 2007), there are 2 tables - "Source" and "Expected_Output"
1: Table "Source" contains values CONCATENATED in a single row with concatenation string ' OR name = ' With limited knowledge in Access, i tried to perform text-column trying to see if some delimiter could be removed, it messed-up the expected output.
In the attached DB, table "Expected_Output" contains the required output.
2. DB should have the ability to: the values that we obtained in the "Expected_Output" should be converted from multiple rows to single row with concatenations as in the "Source" table Once again i tried using the ' OR name = ' in the query for concatenation and failed again.
View 9 Replies
View Related
May 8, 2005
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access?
This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like,
"Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
View 2 Replies
View Related
Apr 23, 2006
So I have a project where I have to get employees to use infopath to fill out details in a form and then submit it, the form gets saved in an access database. I have all that setup and it works perfectly. I uploaded the forms on a sharepoint site and when I open them up from my computer they submit properly to the databse. However when I open up the forms from another computer they don't submit to the database. I know why it does that (because the databases are not on every computer). I can't however figure out a way to solve this problem so ANY help would be amazing. I was thinking to have the access databse on sharepoint as well so the users can download it but won't that just make it that every single computer will have a different saved database? This problem is driving me crazy.
Thanks in advance for all the help.
View 2 Replies
View Related
Jun 27, 2005
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
View 12 Replies
View Related
Oct 19, 2007
Hello All
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
Regards
Terence
London
View 3 Replies
View Related
Jan 11, 2008
Hi everyone!
I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:
Week: Format([Date];"yyyy ww")
The problem is that when I go to datasheet view, it is sorted wrong.
It looks like this:
2007 1
2007 10
2007 11
2007 12
.
.
.
2007 19
2007 2
2007 21
But I want it like this;
2007 1
2007 2
2007 3
2007 4
.
.
.
2007 9
2007 10
2007 11
and so on...
The other column has the rihght values for each week, but I need it in the right order...
What am I doing wrong? Or how can I fix it?
Thanks in advance
Jake
View 8 Replies
View Related
