Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Those who don't understand Linux are doomed to reinvent it, poorly. -- unidentified source

devel / comp.databases.ms-access / Passing a variable to SQL

SubjectAuthor
* Passing a variable to SQLRay Pearson
+- Re: Passing a variable to SQLRon Weiner
`- Re: Passing a variable to SQLRon Paii

1
Passing a variable to SQL

<411217df-e1c1-47a9-b09c-a79ef07f80dfn@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=536&group=comp.databases.ms-access#536

  copy link   Newsgroups: comp.databases.ms-access
X-Received: by 2002:ac8:5a07:: with SMTP id n7mr10452885qta.197.1637094511178;
Tue, 16 Nov 2021 12:28:31 -0800 (PST)
X-Received: by 2002:aca:af42:: with SMTP id y63mr4662267oie.167.1637094510961;
Tue, 16 Nov 2021 12:28:30 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-access
Date: Tue, 16 Nov 2021 12:28:30 -0800 (PST)
Injection-Info: google-groups.googlegroups.com; posting-host=2401:7840:193:8100:d075:908a:b871:e202;
posting-account=k3rk_AoAAAAuxfxRbypX36Qda6i2cQ1y
NNTP-Posting-Host: 2401:7840:193:8100:d075:908a:b871:e202
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <411217df-e1c1-47a9-b09c-a79ef07f80dfn@googlegroups.com>
Subject: Passing a variable to SQL
From: rwpearso...@gmail.com (Ray Pearson)
Injection-Date: Tue, 16 Nov 2021 20:28:31 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 14
 by: Ray Pearson - Tue, 16 Nov 2021 20:28 UTC

I have the following code -

Dim varx As String
varx = DLookup("[SubYear]", "[tblConfiguration]")

Set db = CurrentDb()

strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, FarNorth.Status, FarNorth.MOJNo, Sub2.SubID, Sub2.SubYear, Sub2.AmountPaid " & _
"FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID " & _
"WHERE (((FarNorth.Status)='Active') AND ((Sub2.SubYear)= varx) AND ((Sub2.AmountPaid)Is Null) AND ((Len([FarNorth].[Email]))>0));"

What am I doing wrong? varx is actually a Year eg 2022.

TIAA - Ray

Re: Passing a variable to SQL

<sn1am4$uca$1@dont-email.me>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=537&group=comp.databases.ms-access#537

  copy link   Newsgroups: comp.databases.ms-access
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: rw...@domain.com (Ron Weiner)
Newsgroups: comp.databases.ms-access
Subject: Re: Passing a variable to SQL
Date: Tue, 16 Nov 2021 17:17:38 -0500
Organization: WorksRite
Lines: 38
Message-ID: <sn1am4$uca$1@dont-email.me>
References: <411217df-e1c1-47a9-b09c-a79ef07f80dfn@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 16 Nov 2021 22:17:41 -0000 (UTC)
Injection-Info: reader02.eternal-september.org; posting-host="f5e240e0504942f4ffd854cae7cd8380";
logging-data="31114"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/fC+FlI9R0+hOmOUAZxgDr"
Cancel-Lock: sha1:RzawHLP6OhlmgZR16ud/PK6hzjA=
X-Antivirus-Status: Clean
X-Newsreader: MesNews/1.08.06.00-gb
X-Antivirus: AVG (VPS 211116-12, 11/16/2021), Outbound message
X-ICQ: 1701145376
 by: Ron Weiner - Tue, 16 Nov 2021 22:17 UTC

It happens that Ray Pearson formulated :
> I have the following code -
>
> Dim varx As String
>
> varx = DLookup("[SubYear]", "[tblConfiguration]")
>
> Set db = CurrentDb()
>
> strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname,
> FarNorth.Email, FarNorth.Status, FarNorth.MOJNo, Sub2.SubID, Sub2.SubYear,
> Sub2.AmountPaid " & _ "FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID =
> Sub2.SubID " & _ "WHERE (((FarNorth.Status)='Active') AND
> ((Sub2.SubYear)= varx) AND ((Sub2.AmountPaid)Is Null) AND
> ((Len([FarNorth].[Email]))>0));"
>
> What am I doing wrong? varx is actually a Year eg 2022.
>
> TIAA - Ray

If sub2.SubYear is text try changing :

AND ((Sub2.SubYear)= varx)

To :

AND ((Sub2.SubYear)= ‘“ & varx & “’)

If it's a number try :

AND ((Sub2.SubYear)= “ & varx & “)

The easy way to debug these things is to put a breakpoint on the first
instruction after the strSql = assignment. Then debug.print strSql.
Finaly copy the the full sql statement from the immediate windopw into
the Sql view of a new query and let access show you the problem.

Ron W

Re: Passing a variable to SQL

<d48796c1-4861-4e7f-84f9-094b5ec8d550n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=538&group=comp.databases.ms-access#538

  copy link   Newsgroups: comp.databases.ms-access
X-Received: by 2002:a05:620a:1294:: with SMTP id w20mr23414506qki.197.1637256123672;
Thu, 18 Nov 2021 09:22:03 -0800 (PST)
X-Received: by 2002:a54:4f82:: with SMTP id g2mr8662129oiy.134.1637256123095;
Thu, 18 Nov 2021 09:22:03 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-access
Date: Thu, 18 Nov 2021 09:22:02 -0800 (PST)
In-Reply-To: <411217df-e1c1-47a9-b09c-a79ef07f80dfn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=65.30.79.18; posting-account=RurhpQoAAACBjKrwaxvElRMdPO3AZiPP
NNTP-Posting-Host: 65.30.79.18
References: <411217df-e1c1-47a9-b09c-a79ef07f80dfn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <d48796c1-4861-4e7f-84f9-094b5ec8d550n@googlegroups.com>
Subject: Re: Passing a variable to SQL
From: ron81...@gmail.com (Ron Paii)
Injection-Date: Thu, 18 Nov 2021 17:22:03 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 18
 by: Ron Paii - Thu, 18 Nov 2021 17:22 UTC

On Tuesday, November 16, 2021 at 2:28:33 PM UTC-6, Ray Pearson wrote:
> I have the following code -
>
> Dim varx As String
>
> varx = DLookup("[SubYear]", "[tblConfiguration]")
>
> Set db = CurrentDb()
>
> strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, FarNorth.Status, FarNorth.MOJNo, Sub2.SubID, Sub2.SubYear, Sub2.AmountPaid " & _
> "FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID " & _
> "WHERE (((FarNorth.Status)='Active') AND ((Sub2.SubYear)= varx) AND ((Sub2.AmountPaid)Is Null) AND ((Len([FarNorth].[Email]))>0));"
>
> What am I doing wrong? varx is actually a Year eg 2022.
>
> TIAA - Ray

((Sub2.SubYear)=" & varx & ")

1
server_pubkey.txt

rocksolid light 0.9.8
clearnet tor