UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Pull Data From Table, Access 2013    
 
   
fogline
post Mar 1 2018, 11:48 AM
Post#1



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


I have a table with Addresses " tblAddress " it may only have 2 to 6 or so addresses.
I want to click a button on my form and pull all of them from that table and concatenate them all in a Long Text field name " txtAllAddress " with " / " in between them.
Like this:
" & Address1 & "/ " & Address2 & "/ " & Address3 & " "

Thanks for any help.
Go to the top of the page
 
theDBguy
post Mar 1 2018, 11:54 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

You could try using this simple function: SimpleCSV()

For example:

Me.txtAllAddress=SimpleCSV("SELECT FieldName FROM tblAddress", " / ")

Hope it helps...
Go to the top of the page
 
fogline
post Mar 1 2018, 12:27 PM
Post#3



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


What i am trying to do is .
I have a truck routing program and the user will enter all of the Pickup and Drop address.
I need to concatenate them like Address1 & "/ " & Address2 & "/ " & Address3
I will use this to do a route with Google Maps.
It works like this:
https://www.google.com/maps/dir/" & Address1 & "/ " & Address2 & "/ " & Address3 & "
This post has been edited by fogline: Mar 1 2018, 12:53 PM
Go to the top of the page
 
fogline
post Mar 1 2018, 01:04 PM
Post#4



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


Thank you the SimpleCSV did work great.
Go to the top of the page
 
theDBguy
post Mar 1 2018, 01:12 PM
Post#5


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it to work. Good luck with your project.
Go to the top of the page
 
fogline
post Mar 1 2018, 01:42 PM
Post#6



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


Here is one test project that works with Google Maps I have that you can play around with.
what I need it to do also is to show Total mileage for each state traveled.
This post has been edited by fogline: Mar 1 2018, 01:52 PM
Attached File(s)
Attached File  Calculate_Distance.zip ( 115.26K )Number of downloads: 14
 
Go to the top of the page
 
orange999
post Mar 1 2018, 02:26 PM
Post#7



Posts: 1,868
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


FogLine,

QUOTE
Here is one test project that works with Google Maps I have that you can play around with.


Interesting project. Do you have any instructions for what actually works in this current version?
Even some point form step1, step2.... would be helpful.
What is you plan for Stop/Leg info (distance and time from A-B, B-C...)?
The metrics by state is interesting and I recall it being asked before. I don't know if the OP ever got it resolved.

Please keep us updated on your progress.

Go to the top of the page
 
fogline
post Mar 1 2018, 05:44 PM
Post#8



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


theDBguy
I am trying to add a WHERE clause in the SimpleCSV
Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripRoute WHERE PdID= 85170524 ", " / ")

Nor can I add a Criteria in the Query.

When I try to run the code it comes back to this not working
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

For some reason the SimpleCSV Function will not support a query with a Criteria in the Query nor WHERE clause .????

All of the code works great with out the Criteria in the Query or WHERE clause
This post has been edited by fogline: Mar 1 2018, 05:45 PM
Go to the top of the page
 
theDBguy
post Mar 1 2018, 05:50 PM
Post#9


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I didn't download your file, so I don't know if qryTripRoute is a parameter query.

Are you saying the following works?

Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripRoute", " / ")

But the following doesn't?

Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripRoute WHERE PdID=85170524", " / ")

If so, what data type is PdID? SimpleCSV() should work with a criteria. If PdID is a Text field, then you could try the following:

CODE
Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripRoute WHERE PdID='85170524'", " / ")

Hope it helps...
Go to the top of the page
 
fogline
post Mar 1 2018, 05:57 PM
Post#10



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA



This did work
Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripRoute WHERE PdID='85170524'", " / ")

and yes the PdID are text fields.

Why would the code stop here:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Go to the top of the page
 
fogline
post Mar 1 2018, 06:02 PM
Post#11



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA



What I'm trying to really do is this:

Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripPDGoogleRoute WHERE PdID= '[Forms]![frmRevenue]![Load ID]' ", " / ")

I need is the WHERE PdID to match the [Load ID] field on the form that is open.
Go to the top of the page
 
theDBguy
post Mar 1 2018, 06:04 PM
Post#12


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out. The code stopped because there was an error in the WHERE clause (it was probably a Type Mismatch error). I did not add an error handler to the function because some developers like to use their own error handlers.

Cheers!
Go to the top of the page
 
fogline
post Mar 1 2018, 06:59 PM
Post#13



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


What I'm trying to really do is this:

Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripPDGoogleRoute WHERE PdID= '[Forms]![frmRevenue]![Load ID]' ", " / ")

What I need is the WHERE PdID to match the [Load ID] field on a form that is open.
Go to the top of the page
 
zaxbat
post Mar 1 2018, 08:12 PM
Post#14



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I have never found a way to make a query pull a value out of a form control. I always have to get the values using VBA....then assemble the SQL into a text string and run it using docmd.runsql mysqltxt
Go to the top of the page
 
fogline
post Mar 1 2018, 08:16 PM
Post#15



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


.
This post has been edited by fogline: Mar 1 2018, 08:18 PM
Go to the top of the page
 
theDBguy
post Mar 1 2018, 08:28 PM
Post#16


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Re: "What I need is the WHERE PdID to match the [Load ID] field on a form that is open."

In that case, you could try it this way:

CODE
Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripPDGoogleRoute WHERE PdID='" & [Forms]![frmRevenue]![Load ID] & "'", " / ")

Hope it helps...
Go to the top of the page
 
tina t
post Mar 1 2018, 09:19 PM
Post#17



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
I have never found a way to make a query pull a value out of a form control.

this is a very common operation in Access. have you had this problem in a specific version of the software? can you provide a sample of a SQL statement (from a query's SQL pane) where the reference does not work?

hth
tina
Go to the top of the page
 
zaxbat
post Mar 2 2018, 12:01 PM
Post#18



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Tina,

My problem with that approach is two-fold....

one----referencing controls seems random to me....do I use bang or dot.....direct or indirect.....based on parent or local....has the control even been loaded yet...is it anywhere in scope.....so many things come into play....

two----problems with complex SQL calls.....I have enough trouble debugging my SQL without adding possibly incorrect control references to the mix....

So, I have found my solution that usually works for me and just tend to stick with it. Can't help but think that anybody else learning Access should do the same or they are likely to run into the same problems that I did....and end up not fully trusting Access like i don't... ha ha ha ha ha ha. In fact I know this to be true judging from all the new posts I see in UA.
This post has been edited by zaxbat: Mar 2 2018, 12:02 PM
Go to the top of the page
 
tina t
post Mar 2 2018, 02:36 PM
Post#19



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
one----referencing controls seems random to me....do I use bang or dot.....direct or indirect.....based on parent or local....has the control even been loaded yet...is it anywhere in scope.....so many things come into play....

form control references are not random, the syntax is specific. i don't understand "direct or indirect.....based on parent or local", unless you're referring to subforms, and nested subforms. again, the syntax is specific, and actually not difficult. you just need to understand how forms/subforms "work". i don't really understand "has the control even been loaded yet...is it anywhere in scope" either; as the developer, it's up to you when you call a query that uses a form reference - you don't do it before the form (and the controls on it) is loaded.

QUOTE
two----problems with complex SQL calls.....I have enough trouble debugging my SQL without adding possibly incorrect control references to the mix....

well, that just goes back to writing form references correctly. once you have that down, it doesn't matter whether the query is simple or complex. i will note here that i'm talking about working with Access forms, and queries against Access tables. i don't have experience with querying against SQL Server data, or other data repositories, so i can't address that one way or the other.

QUOTE
Can't help but think that anybody else learning Access should do the same or they are likely to run into the same problems that I did

really, IMO, i think it's worth investing in the time to learn form/control reference syntax, as it can be used in many places besides query criteria. there are situations where building SQL statements in VBA is useful, and situations where a stored query is just simpler and easier. i wouldn't close the door on either approach, both are good skills for a developer to have in his/her skillset.

as for not trusting Access...well, there have been unstable versions of Access that were not trustworthy. Access95 was a good example. i haven't run into problems with later .mdb versions, but have heard ad hoc stories of problems with one or another of the newer .accdb versions. but unless a developer is working with an unstable version of the software, or encountering some other problem stemming from who-knows-what, i tend to think that problems being blamed on Access are actually the developer's problems. at least, that's been my experience when i hollered about the @!$%# program, and then it turned out to be my mistake! ;)

hth
tina
This post has been edited by tina t: Mar 2 2018, 02:41 PM
Go to the top of the page
 
fogline
post Mar 3 2018, 12:32 PM
Post#20



Posts: 69
Joined: 5-August 15
From: Ringgold, GA. USA


Me.txtAllAddress = SimpleCSV("SELECT address2 FROM qryTripPDGoogleRoute WHERE PdID='" & [Forms]![frmRevenue]![Load ID] & "'", " / ")

WOOOOW DBman that works GREAT. You are the Man.
I want to thank you so much, I have learned so much from all of your post.

Thank You DBman. smile.gif
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 11:06 AM