Modules & VBA :: Passing Date Strings Into Access Chart

Sep 11, 2013

I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:

Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));

So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()

Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));

View Replies


ADVERTISEMENT

Modules & VBA :: Passing Excel Cell Date To Access Query

May 1, 2014

I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.

I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """

This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"

View 4 Replies View Related

Modules & VBA :: Passing Parameter And Date To Query

Nov 10, 2014

I have the following Visual Basic code which I am using to dynamically pass the parameters "SAFP" and date 10/31/2014 to the query:

Option Compare Database

'------------------------------------------------------------
' Run_Risk_Assessment_Report
'
'------------------------------------------------------------
'Original macro code
Function Run_Risk_Assessment_Report()
On Error GoTo Run_Risk_Assessment_Report_Err
Dim dbs As DAO.Database
Dim test1 As DAO.QueryDef

[Code] ....

When I run the query, i get the error "Item not Found in this collection"

View 14 Replies View Related

Limit Chart To Date Range / Chart Isn't Updating

Jun 28, 2015

I have a form with a chart , 2 textboxes for start date and end date and a button to filter the data( filter the data by date range) How do i continue from that? The chart isnt updating.

View 5 Replies View Related

Modules & VBA :: Creating Excel Pivot Chart In Access

Jan 21, 2014

I've written this code in Access to create a pivot chart in Excel. All the code listed is good except for the last line. The last line creates another instance of Excel. I can't figure out what the correct syntax is.

Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open("c:chi estpivot.xlsx"
wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

[code]....

View 14 Replies View Related

Modules & VBA :: Passing Variable From Excel To Fire Access Query

Dec 17, 2014

I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.

View 4 Replies View Related

Modules & VBA :: How To Update Data For A Chart Graph In Word Document From Access

Jul 29, 2013

Is it possible to update the data for a chart graph in a word document from Access using VBA?

View 4 Replies View Related

Queries :: Date Time Calculation From Strings

Oct 9, 2014

I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:

datetime2 - datetime1.

I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!

View 2 Replies View Related

Strings Within Strings Using Expression Builder

Jun 6, 2007

Hello

I have two tables with names of people in different forms.

table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname

table2: [surname]

I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]

I've tried using

Like "*"&[surname]&"*"

This returns everything rather than the similar names.

I think this should be simple but can't work it out.

View 1 Replies View Related

Modules & VBA :: Form With Multi-criteria Searches / Uses Strings And Filters

Jan 23, 2014

I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields. As of now, it works as long as all four criteria boxes aren't null.I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.

Code:

Me.Filter = "[Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) & "
AND [HR Holder] = '" & Me.HRCombo & "'" & " AND [Building] = '" & Me.BuildingCombo
& "'" & " AND [Room] = '" & Me.RoomCombo & "'"
Me.FilterOn = True
Me.Requery

I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end. Here's the code I used for this, and, with my limited knowledge, I can't get it to work.

Code:

Dim StrA as String, StrB as String, StrC as String, StrD as String
If Me.Keyword is null then
StrA = "*"
else
StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34)
End If

[code]....

View 14 Replies View Related

Modules & VBA :: Send / Receive Small Text Strings Over A Network

Jul 20, 2015

Small piece of code that can send / recieve small text-strings over a network. I finally got it to work, but forgot to think ahead . Right now it only works in a formular but i really need to be a function with input / output.

My problem is that i am using the "Withevents" to call the Ostrosoft Winsock network module and "Withevents" does not work in functions.

How to build this into a function ?

Code:
Option Compare Database
Option Explicit
Dim sBuffer As String
Dim spage As String
Dim WithEvents wsTCP As OSWINSCK.Winsock

[Code] ....

View 2 Replies View Related

Modules & VBA :: Extract Strings From A Field In Table (Separated By Colon)

Oct 21, 2013

I have a field in a table that I'd like to extra the texts from. The are stored in below format (separated by colons). They don't always the same amount of characters and not all of them have the same amount of texts.

Some may only have Text1:Text2::, while some may have Text1:::Text4.

Text1:Text2:Text3:Text4

View 8 Replies View Related

Access Sql Replace Statement Null/empty Strings

Jul 31, 2007

Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:

SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance
FROM [400 Student Marks];

[1] being the first register week then I would have concatenated with [2] etc...

This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.

Thanks for any advice.

View 1 Replies View Related

Passing Date To A Query

Jul 31, 2005

My form has a date field (with a default of today-105 days)( and can be updated by the user) which is part of the following query

SELECT dbo_CM_AS_LG.AS_LG_DT, dbo_CM_AS_LG.AS_LG_WO, dbo_CM_EQ_MA.EQ_MA_ID, dbo_CM_EQ_MA.EQ_MA_DE, dbo_CM_EQ_MA.EQ_MA_PS
FROM dbo_CM_AS_LG LEFT JOIN dbo_CM_EQ_MA ON dbo_CM_AS_LG.AS_LG_TP = dbo_CM_EQ_MA.EQ_MA_NO_P
WHERE (((dbo_CM_AS_LG.AS_LG_DT)>[Forms]![F_Q_RISC_SWAPS]![SearchDate]) AND ((dbo_CM_EQ_MA.EQ_MA_DE) Like "RISC/6000*" Or (dbo_CM_EQ_MA.EQ_MA_DE) Like "ibm power pc*"))
ORDER BY dbo_CM_AS_LG.AS_LG_DT;

The query is thr root query for the form, but when the form opens the default date has not been set and I get an error.
Is there a good way to pass a parameter into a query ?
I have tried running the qury using the ON Open Property too, which works until I try and call this form from another one.

I'm sure there's a simple answer, I just can't see the wood for the trees (yet)

Cheers

View 1 Replies View Related

Passing A Date Into Between Expression

Jun 10, 2012

I have a query with an expression as follows in criteria:

Between #1/1/2012# And #1/1/2012#

I want to pass into that expression a value from another table.

So, I created a table, DateRange, with fields StartDate and EndDate

Then, I modified my expression as follows:

Between [DateRange]![StartDate] And [DateRange]![EndDate]

Which, of course, didn't work, as the expression didn't know which 'row' to use.

View 4 Replies View Related

Show Grouped Date In Chart

Jan 18, 2006

Hi!

I use MS Graph and need show 2 data types:
1. Invoice Value, EUR
2. Date
Date axis group records by month, for Example Period from 2005.01.01 And 2006.01.01 has 12 positions in Date Axis.
Her I have problem: I need show sum of Values for every month.
For example in Period from 2005.01.01 till 2005.02.01 I have 3 Values: 200, 500, 600EUR. Her I need to show 1300EUR, and so every month. Her is my strandart code:

Dim strSQL1 As String
strSQL1 = "SELECT Date As Date, Value FROM Table WHERE (Date BETWEEN 2005.01.01 And 2006.01.01)"
Me!chrStatistik.RowSource = strSQL1

Thank You in advace for help.

View 5 Replies View Related

Print Date Criteria In Chart?

Aug 17, 2012

I created a query and chart for a date range in my database.

My query asks for input from the user to provide a date range.

I need to print that date range, the query is based on, under the heading for the chart.

View 1 Replies View Related

Passing A Date Field To A Query

Apr 9, 2007

Hi All,

I wonder if anyone can help me? I am at the stage now of building a query in design view. Rather than using a dynamic parameter field to capture a range of dates (between...[InputDate] And [InputDate]), I have created a text box in a form and want to pass the contents to my query. I have got this to work providing the variable that is passed is 'text'. I need to pass two dates though. When I put paths to the forms textbox in the 'Between' statement above, it just doesn't return any records. I think Access sees these text boxes as 'text' rather than 'dates'. I don't know how to change it so Access sees these as dates. Any ideas?

View 3 Replies View Related

Date Format When Passing Data To An Email

Aug 31, 2005

I have a button on a form that creates an email and inserts fields from my database. I have a field that is set to Long Date format type, but when it populates the email, it shows as a Short Date format type. Is there anyway to retain the Long Date format?

View 1 Replies View Related

Passing Date Field To Word Document

May 16, 2005

Hi,

I have a control on a form which opens up Word and completes fields in the Word document with data on the form. It basically fills in an invoice form.

Everything works ok apart from when the invoice date and order date fields are passed. In the tables and in the forms, the dates are in Long Format ie '17 May 2005'. However, when they are apssed to the Word document, they are in a shortened format ie '17/05/05'. I am required to show the Long Format but am at a loss to find out how to achieve this.

I use bookmarks in the Word document and the code on my form relating to these fields are:

.ActiveDocument.Bookmarks("OrdDate").Select
.Selection.Text = (CStr(Forms!frmOrders!OrdDate))
.ActiveDocument.Bookmarks("OrdDateInv").Select
.Selection.Text = (CStr(Forms!frmOrders!OrdDateInv)

The Word document cannot be changed as far as I can see. I believe the answer could be to change the code above to change the format, but cannot get anything to work?

Your assistance would be helpful.

Thanks

View 1 Replies View Related

Passing A Date Variable To Anaother Form

Aug 5, 2006

In form1 I've a command button to open form2.

Code in form1:

Private Sub button_Click()
On Error GoTo Err_button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "form2"
stLinkCriteria = button.Caption
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_button_Click:
Exit Sub

Err_button_Click:
MsgBox Err.Description
Resume Exit_button_Click

End Sub

button.Caption contains a valid date value

In form2 I want to display the date in a label.

Code in form2:
Private Sub Form_Load()
Dim DatePassed As Date

DatePassed = CDate(Me.OpenArgs)
label.Caption = DatePassed
End Sub

On executing I get an error like "Invalid use of null value."

Who helps?

View 5 Replies View Related

Forms :: Date Not Passing From Table To Form

Apr 12, 2013

I have the date set in the table and it shows up fine in the tblDate cell but doesn't pass through to the txttblDate in the form. I did have it working just fine until I rebuilt my database.

View 14 Replies View Related

Can Form UseMicrosoft Excel Chart Instead Of Microsoft Chart Wizard

Mar 22, 2006

Hi all,

I'm trying to create a chart in a form, this is not a problem, however, the chart types available are a bit limited compared to if i inserted a Microsoft Excel Chart object.

I'd do that except I'm trying to create a chart based on a query.

Is there a way to make the chart wizard use the Microsoft Excel Chart object as its chart creator so I have access to the chart types available in that object?

any assistance much appreciated.

thanks all.

regards

keji

View 1 Replies View Related

Modules & VBA :: Passing Image To Another App?

Dec 27, 2013

I want to use an On Click event on an image field on a subform to fire up either Windows Photo Viewer or Corel PaintShop Pro with that image active - but don't really know where to start. DoCmd, Shell ?

View 5 Replies View Related

Modules & VBA :: Display A Pivot Chart

Nov 18, 2013

I have a need to display a pivot chart by clicking a button on my menu screen. At the moment my button will just display the query results and if I then click on View/PivotChart View my chart displays.

Code:
Private Sub Command316_Click()
DoCmd.OpenQuery "R06X - OOS Chart", avViewNormal
End Sub

View 4 Replies View Related

Modules & VBA :: Identify Strings Into 2nd Table Records Then Update 1st Table

May 5, 2015

MS Access 2013: I have two database tables as below:

tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")

tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.

Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".

...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.

Attached sample database for more details.

View 4 Replies View Related







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