How to extract Month & Year together from a Date

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

Any idea ...?


Post your answer or comment

comments powered by Disqus
I want to exclude the time value from a date/time variable without affecting the date/time format of the user.

For example if the date/time variable is "1/12/2007 09:12:46", I want to get the value as "1/12/2007".

I dont want to use the format("dd/mm/yyyy") function because it will affect the user format for the dates. For the same reason, I dont want to use the function day(), month(),year().

How can achieve this without affecting the user format?


Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

Does anyone have an idea how to extract the first paragraph of a memo field of a table to be used in a query? Thanks...

Ok, I've reviewed almost every post thus far about how to extract a specific text string from a text/memo field. I have not yet found how to extract multiple text strings from a single text field that fit the same criteria. Let me be more specific...I am using the Mid and InStr functions to capture a 10-character alphanumeric text string from my text fields (i.e. K034567890). I've created multiple Mid and InStr fields for every possible letter (A0, B0, C0, etc.). It always has a letter, then a 0, then 8 digits. My problem exists when the same letter and then 0 can occur multiple times in the same text field (J012345678, J012345679, J012345670, etc.). How can I fix my query to pull out EVERY POSSIBLE letter, 0, and then 8 digit sequence even if the criteria is exactly the same. I would prefer help with a query-based answer. If someone has a solution venturing into the world of VBA, you'll have to REALLY dumb it down for me. Thank you for any help you can provide.

I need to fill a year field on a form from a date field on the same form when the date field is filled in by the user.

I have a column of date in mm/dd/yyyy format in my table and it's data type is text.
My objective is to extract only month and year from that table with a specific name.Example:
ID Name Date
01 SAM 09/02/2012
02 DAVID 08/05/2012
I want the exact query to get the O/P.I am using in front end and access 2003 in the system.

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



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

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

I have a table with about 100 fields, most of which have a defaultvalue.
These defaults need to be changed every couple of months or so. It is tedious to go through the table design directly. How can I code a form containing combi-boxes based on the table, which will allow me to select the new default values. ( I have managed to do this for *one* field using

Dim dbs As Database, tdf As TableDef
Dim fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to table.
Set tdf = dbs.TableDefs!tblcont

Set fld = tdf!TSeonID
' Set field properties.
fld.DefaultValue = Me!TSeonID.Value

Set dbs = Nothing

and using this code on a 'on change' event from a combo box.

BUT... i would have to re-code this for 100 combo boxes!!

Any suggestions please.

Detailed question didn't fit in the title : How to prevent updating existing values in a query field when the value in the related table field changes.

I have a table Product, which has fields product number, description, price, etc. I then have a query ProductQ, which has all the fields of the table Product, and some calculated fields (like tax from %, total price) based on the Product fields. This query ProductQ is used in other queries for making Purchase orders, quotation and invoices.

I have been using this structure since last one year. Now some prices have been revised, but what happens is if I edit the price in the table Product, then all the existing records in Purchase Orders and Invoices reflect the edited price. I want to change the price of only future records, not the existing ones. How do I do it ?

There are no relationships as such, as there is only one table which has the price and that is Product table.

My present option is to duplicate all product records except the product number and then change the prices in the duplicated records and start using them instead of the old records, but I would really like to have a permanent solution, as the price change will be very frequent. Thanks for these forums

I have a recordset(“lifting program”) which contains 60 fields of data.
Between the fields of 25 to 40, it contains the quantity of products which are being sold to the customer. However, in these fields, it only contains records of those products that are required by the customers. For those products that are required by the customer, it will show null records.

For example, if the customer requires product D and product H, then for field28 which is product D, the record will show 1000 and field32 which is product H, the record will show 800. The rest of the fields (between 25 to 40) will have null records.

For each row, there is a unique shipment_no (field1) being assigned. There is a max of only 8 non-null datas from each row.
How do you obtain the non-null data from the fields between 25 to 40 for shipment_no = 3471 ? I would like to display the non-null data on controls in a form.


	Private sub extract_Click()
    Dim db As Database    Dim Rst As Recordset         Set db = CurrentDB()        Set Rst = db.OpenRecordset("lifting

With Rst
            .findfirst ( “shipment_no” = 3471)

            ‘ how to write the loop to obtain non null data
            ‘ For .fields(25) to .fields(40) 
            ‘           Me.txt_pd1 = 1st non-null record
            ‘           Me.txt_pd2 = 2nd non-null record
            ‘           ….
            ‘           Me.txt_pd8 = 8th non-null record
            ‘ Next

End With

End Sub

Using access 2007, I have a table with responses from a survey in various fields(columns) and I want to use a combo box to add up the rows. I've looked and most combo boxes refer to using a form not a table. How would I use a combo box to extract the rows data from a table?


How do you pass the data from a listbox to a subform?

When the data is transferred, it would automatically create a new record in the subform.

Would this work with multi-selection?

Hi all .i got the following inside a textbox inside my vb6 form. I am looking for a way to ripp the song path and title from it and use them and insert them to mysql datbase. But i do not know how to ripp those part
i be happy that an expert tell me how i can do it using vb6. The number of song path are not know in before and the parts
that i want to write to mysql are shown in bold.Thanks

data inside my texbox:


Hey guys,

So I'm totally new to access, and I know nothing about beginner alert... :-)

But basically I've created a series of forms from my tables in order to enable easy data entry, and viewing.

I think I've got the data entry part down - I created a button on each of my forms (using the button wizard - "add new record") which enters the all the info I've entered into my form into the fields of my table...which is perfect.

But I can't figure out how I can use the same form to do the reverse - pull up all the data from a particular record in my table, and view it in the form.

I basically want to be able to type in a participant's ID number (its a database for clinical research) into the form somewhere, and then have it show the rest of the data for that person from that particular table.

I've tried creating another button and using some of the record navigation options (like "find record") but this does not seem to be working - it keeps telling me the record cannot be found, even though the record DOES exist in the table.

Sorry, I'm sure this question is incredibly dumb but hey, we all start somewhere... :-)




I have a table with employees' names. Along with the employees' names, I have department and job for each employee. The table has only one employee.

I have a second table with training material. The training material is one field in the second table. The next field is department and the last field is job. This table is to decide which departments and jobs need to be trained on the training material.

Finally, I have a third table of training documentation. It documents the training each employee has received.

Now, I need to Query the following:

When an employee is selected from the first table using a drop down form, I need it to fill in the department and job for that employee so I can store the information in another query. In other words, there is only one Bob Miller. I need to be able to select Bob Miller's name and not only see his department and job but be able to store that information. I see that a combo box will allow me to view the information, but I don't know how to actually store that value for later use.


I have a graph that is pulling data from a query. What I am trying to do is add a total for the year. I have two columns, one is, Dateofaudit and Total which shows the total number of findings for a particular finding that I enter myself. I tried using the sum and Total functions but can't get it to work.

Here is what I am trying to do:
Total for dateofaudit that equals the year 2011 etc.

dateofaudit Total
01/10/11 2
02/12/11 7

Total for 2011 would be 9.

I'm not sure if I should do this in the query or report.

I have a listbox displaying results from a string search in a text box of a query. Im trying to build an event where the user can doubleclick on the selected result and it will take them to that record. This is the code I have but it won't take me to the selected record in the form. It takes me to a new blank record in that form. The fields in the query are on the attached photo.

Private Sub SearchResults_DblClick(Cancel As Integer)

DoCmd.OpenForm "WorkOrderF", , , WorkOrderID = " & SearchResults"

End Sub

Private Sub WorkOrderSearch_Change()
Dim vSearchString As String
vSearchString = WorkOrderSearch.Text
SrchText.Value = vSearchString

If Len(Me.SrchText) 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If
Me.SearchResults = Me.SearchResults.ItemData(1)
If Not IsNull(Len(Me.WorkOrderSearch)) Then
Me.WorkOrderSearch.SelStart = Len(Me.WorkOrderSearch)
End If
End Sub

All help would be greatly appreciated! Thank you!


I need help with following: in query I made I have one short date-type field called "CurrentDate". On my PC, default short date format is like this "MM/dd/yyyy".

In my query I have one more field which looks like this "ExtractDate: Left([CurrentDate],2)". This is how it looks like:

Task of this field is to "extract" first wo values of field "CurrentDate". So for example if value of field "CurrentDate" is "07.02.2011" then field "ExtractDate" will show value "07" and that works very good but recently I had to change short format date on my PC to this one "dd/MM/yyyy" so my field "ExtractDate" is now showing value "02".

Im not sure how to write function in query which will "extract" month from this type of date format "dd/MM/yyyy". How to single out value "02" from this type of date "07.02.2011"??

Any help is appreciated and many thanks in advance for prompt replys!



Can somebody please tell me how to replace the year (2004) with a variable.




I am working on a form with Access XP and have been trying various solutions to a problem I have. I would like a check box to be checked after selecting from a combo box. If the combo box is blank, the check box should also be blank.

Anyone have any ideas of how I might do this? I've tried various things with conditional formatting and code, but I'm new at this and nothing I've done works.

Thanks for any help!


I would like to construct one or more functions that will allow me to extract each string element from a dot notation populated field. An example of the content of such a field (perhaps named [Org_Structure]), would be:

Organization.Division Name.0001 - Region Name.123456 - Area Name.A Rather Long Location Name

In populating the [Org_Structure] field, I'll be able to strip out all special characters between the "." (dot) delimeters except the "-" (hyphen) character.

So what I need to be able to do is then use a function to strip out each of the values between the dot notation delimeters into separate fields.

I have a feeling that this can be done using the "instr" function in conjunction with perhaps the "left" function or some other combination, but I'm not sure what would be the best way to approach this so that I can easily create a separate function for each element to be extracted to a separate field.

Can anyone help me figure out the best way to approach this?

Any assistance would be most appreciated.



History/background: We have an older system that generates txt reports. I take these txt reports, then using Excel, I append the report headers to each record... creating a datatable. I then import this table to Access. I have not been able to figure out how to have Access perform this. In Excel, I use an "if" statement.

Please... any thoughts or direction would be extremely appreciated. Even "off the top of the head" stuff. I'm stuck.

A Form (displaying quotes) needs to support visually selecting a quote to be the quote utilized for a particular part.

I was thinking to have a subform displaying the list of quote records.

Further I was thinking to on the main form have a series (vertically) of radio buttons. "Select which quote to use for XYZ part".

Then I thought of the fact that perhaps there are more quote records than can fit on the screen, thus vertical scroll bar gets dynamically added, and good by to 1-to-1 association with the dynamically created radio buttons.

So, open to other suggestions on how to select the one quote.

aaahhh... a combo box might work. Populate that one control based on the quote records associated with the part record.

Any other suggestions come to mind?

Not finding an answer? Try a Google search.