Re: Import Access records to excel (parameter is a called funct)

From: PSKelligan (PSKelligan_at_discussions.microsoft.com)
Date: 09/28/04 

Date: Mon, 27 Sep 2004 17:17:02 -0700

Myrna,
Yes, the -2, -1 actually were just my attemts at troubleshooting the code.
It did not work with or without it. It querys alright but does not roll back
to previous months. tripple checked my parenthasies but no clue as to why it
isnot worki

Thanks,

Patrick

"Myrna Larson" wrote:

> If it is "almost" working, then it looks to me as if you've introduced a bug
> in Jamie's code. For the first Iff function, since it's -Iff (rather than
> +Iff) the 2 and 1 inside the parentheses should be positive, not negative. If
> you make them negative you are adding 2 or 1 month instead of subtracting.
>
> FWIW, When I was doing queries by passing the SQL string, I had to pass dates
> in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing
> a date variable as such didn't work for me.
>
> So my code (in part) looked like this:
>
> Dim SQL As String
>
> SQL = "SELECT PrTicker, PrNAV FROM Prices WHERE PrDate = DDD" & _
> " ORDER BY PrTicker"
> SQL = Replace(S, "DDD", SQLDate(TheDate))
>
> The function to format the date correctly, SQLDate, looked like this:
>
> Function SQLDate(ADate As Date) As String
> SQLDate = Format$(ADate, "\#mm/dd/yyyy\#")
> End Function
>
> I like to write out the SQL statement using "place holders" for the variables,
> then use the Replace function to insert the correct values. I would write your
> code as
>
> Dim sDate As String
> Dim eDate AS String
> Dim SQL As String
>
> sDate = SQLDate(DateSerial(Year(Now()), _
> Month(Now()-IIf(Day(Now())<16,2,1),16))
> eDate = SQLDate(DateSerial(Year(Now()), _
> Month(Now()-IIf(Day(Now())<16,1,0),15))
>
> SQL = "SELECT * FROM tblHistory WHERE (tblHistory.Date_Updated) Between
> SDATE and EDATE ORDER BY tblHistory.date_Updated DESC;"
> SQL = Replace(SQL,"SDATE",sDate)
> SQL = Replace(SQL,"EDATE",eDate)
>
> Then open the record set with the SQL statement.
>
>
> On Mon, 27 Sep 2004 09:29:02 -0700, "PSKelligan"
> <PSKelligan@discussions.microsoft.com> wrote:
>
> >Hello again,
> >
> >I tried to add your sql to my query and it seemed to work ok except for the
> >-IIF portion of the statement. Here is my statement:
> >
> >SELECT *
> >FROM tblHistory
> >WHERE (((tblHistory.date_Updated) Between
> >DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And
> >DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15)))
> >ORDER BY tblHistory.date_Updated DESC;
> >
> >Any syntax isues that are preventing this? Your little bit of sql, if it
> >will work in this statement will make all my problems go away.
> >
> >Thanks,
> >
> >Patrick
>
>

Relevant Pages

  • Re: Form/Subform
    ... Private Sub Fullname_NotInList(NewData As String, ... Dim sql As String ... Dim strNames() As String ... DoCmd.RunSQL sql, False ...
    (comp.databases.ms-access)
  • Re: recordset.clone
    ... Dim SQL As String ... moment i open my form with the subform it applieas already the filters... ...
    (microsoft.public.access.formscoding)
  • RE: ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)
  • ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)