remains (1 Viewer)

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
Hello!

I have some tables.

Thank you!
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 11:20
Joined
Mar 14, 2017
Messages
8,738
You could probably utilize some Left, Right, Len, and/or Mid functions to figure out which part of the string to replace with "".

In fact, if the only pattern that exists is what you posted, you might even just use: Left(cell,find("#",cell)) (excel formula), or Instr() for vba.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
Hi. Rather than exporting your table, you could try creating a query that extracts only the data you want and then export the query to Excel.
 

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
Hi. Rather than exporting your table, you could try creating a query that extracts only the data you want and then export the query to Excel.
Hi. Rather than exporting your table, you could try creating a query that extracts only the data you want and then export the query to Excel.

I think I understand what you mean but in my project, I'm looking for the user to do everything from the form

But I'm not sure how to do this.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
I think theDBguy means you would amend the data on the fly in the query?
Code:
tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family
so in the query
Code:
NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH
 

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
I think theDBguy means you would amend the data on the fly in the query?
Code:
tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family
so in the query
Code:
NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH

Sorry, I'm not too familiar with Query calculations
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
I have an image of a field that I want to remove the #......# from the excel but still want to have the hyperlink work in the form.

Thank you
That is not what you originally asked?, you just asked to remove anything after the #. :(
I believe you would need to leave it as it is if you still want the link to work.

Not something I have done, and I'm very busy trying to get a laptop back up and running, so I will bow out. Sorry.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:20
Joined
May 7, 2009
Messages
19,169
modify your query to look like this:

Scale: Replace(Mid([ScaleName], Instr([ScaleName], "#") + 1), "#", "")
 

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
That is not what you originally asked?, you just asked to remove anything after the #. :(
I believe you would need to leave it as it is if you still want the link to work.

Not something I have done, and I'm very busy trying to get a laptop back up and running, so I will bow out. Sorry.

My apologies. I probably should have been more specific. I want the hyperlink to work in the table/form. But when I export the data from the form, I want to have the hyperlink (#....#) removed in the exported file.

I'm not sure if this is possible.
 

Isaac

Lifelong Learner
Local time
Today, 11:20
Joined
Mar 14, 2017
Messages
8,738
Did you try what I posted to correct the problem post-export?
Alternately, you can correct it pre-export as several have said. You need to create a query that strips out everything after the # sign, and incorporate that query into however you are currently giving the user the tool to export it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
My apologies. I probably should have been more specific. I want the hyperlink to work in the table/form. But when I export the data from the form, I want to have the hyperlink (#....#) removed in the exported file.

I'm not sure if this is possible.
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?
I *thought* you said you wanted the link to work in Excel as well?
 

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?
I *thought* you said you wanted the link to work in Excel as well?

One sec...
 
Last edited:

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?
I *thought* you said you wanted the link to work in Excel as well?

Your code does work in the query;

Thank you
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
You remove the links in the export. Instead of perhaps using the table directly, you now use a query for the same fields and amend those with hyperlinks in the same way that has been demonstrated.?
So apply the same logic to your union query?
 

N1234

Member
Local time
Today, 14:20
Joined
Jun 22, 2020
Messages
64
You remove the links in the export. Instead of perhaps using the table directly, you now use a query for the same fields and amend those with hyperlinks in the same way that has been demonstrated.?
So apply the same logic to your union query?

I never called the tables directly, they were put in a union query and I called that union query in a form.

I can use a query for the same fields but how do I add a hyperlink to a query? There's nothing in the design form that allows me to adjust the data type.

I just called the new query with the code you sent me and I can export without the hyperlinks, but when I use them in the forms, the hyperlink no longer exists and I get rows of "#Name?" in those fields.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
In some records in scalename you do not have any #, so you need to test for the character first.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
The ones without #, I didn't put in any hyperlinks in that table.
So which table are we talking about.?
You have put up a DB with no instructions on what needs to be done to repeat the problem? :(

I just looked at the HLinkRemove query and worked backwards.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
Right, I could not see the Export button the form is so large.
Your source for your tempquery is "Select * from [tblTheme Query] where [Theme]Like '*CH*'"

So you would need to amend that string to reflect the fields being used and not use * ?
I was expecting a dedicated query?
Not sure how to implement it otherwise.?

For the benefit of others the code is
Code:
Private Sub Command28_Click()
10    On Error GoTo Command28_Clickerr
          Dim fDialog As Office.FileDialog
          Dim vSelected As Variant
          Dim sFolder As String
          Dim myquery As String

20        Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
30            With fDialog
                  ' Set the title of the dialog box.
40                .Title = "Select Folder to Export Tables To In Excel Format"
50                .Filters.Clear
              
                  ' Show the dialog box. If the .Show method returns True, the
                  ' user picked at least one file. If the .Show method returns
                  ' False, the user clicked Cancel.
60                If .Show = True Then
70                    For Each vSelected In .SelectedItems
80                        sFolder = vSelected
90                    Next vSelected
100               Else
110                   MsgBox "You clicked Cancel in the file dialog box."
120                   Exit Sub
130               End If
140           End With
          
           
           myquery = currentrsrc
           Dim qrydef As DAO.QueryDef
           Dim db As DAO.Database
           Set db = CurrentDb
           Set qrydef = db.CreateQueryDef("tempQuery", myquery)
           
           
190        On Error Resume Next
200        Kill sFolder & "\exported.XLSX"
210        On Error GoTo Command28_Clickerr
220        DoCmd.TransferSpreadsheet acExport, , "tempQuery", sFolder & "\exported.XLSX"
230        DoCmd.RunSQL "DROP TABLE tempQuery"
240        DoEvents
250        Application.FollowHyperlink sFolder & "\exported.XLSX"
260        Exit Sub
Command28_Clickerr:
    If Err.Number = 3012 Then
        DoCmd.RunSQL "DROP TABLE tempQuery"
        DoEvents
        Resume
    Else
270        MsgBox Err.Number & " " & Err.Description & " " & Erl, vbOKOnly, "Error in Command28_Clickerr"
    End If
End Sub
 

Users who are viewing this thread

Top Bottom