#### I can not sort hyperlink field in the query

Hello
My query is like below, hyperlink field will not be sort in data sheet
view.out put data is like below:

Unit No	Line Size		Fluid Code	Unit No Iso	Pipe Sequence No	Piping
Class	Insulation Code		hyper
100	10		G		100		20014		G03P2		N		10-G10020014-G03P2-N_Sht_3.pdf

100	10		G		100		20014		G03P2		N		10-G10020014-G03P2-N_Sht_4.pdf

100	10		G		100		20014		G03P2		N		10-G10020014-G03P2-N_Sht_1.pdf

100	10		G		100		20014		G03P2		N		10-G10020014-G03P2-N_Sht_2.pdf

my query:

SELECT DISTINCTROW qryiso.[Unit No], qryiso.[Line Size], qryiso.[Fluid
Code], qryiso.[Unit No Iso], qryiso.[Pipe Sequence No], qryiso.[Piping
Class], qryiso.[Insulation Code], tblfilename.hyper, qryiso.[Total Sheet No],
qryiso.[Issue Date], qryiso.[MaxOfIsometric Drawing Rev],
qryiso.[MaxOfIsometric Drawing List Rev], qryiso.[Issued For], qryiso.Remark,
qryiso.[DOC No], qryiso![Line Size] & "-" & qryiso![Fluid Code] & "-" &
qryiso![Unit No Iso] & "-" & qryiso![Pipe Sequence No] & "-" & qryiso![Piping
Class] & "-" & qryiso![Insulation Code] AS [line No]
FROM qryiso LEFT JOIN tblfilename ON (qryiso.[Line Size]=tblfilename.[Line
Size]) AND (qryiso.[Fluid Code]=tblfilename.[Fluid Code]) AND (qryiso.[Unit
No Iso]=tblfilename.[Unit No Iso]) AND (qryiso.[Pipe Sequence
No]=tblfilename.[Pipe Sequence No]) AND (qryiso.[Piping
Class]=tblfilename.[Piping Class]) AND (qryiso.[Insulation
Code]=tblfilename.[Insulation Code])
WHERE (((qryiso.[DOC No]) Like "*001" Or (qryiso.[DOC No]) Like "*003"))
ORDER BY qryiso.[Unit No], qryiso.[Line Size], qryiso.[Fluid Code],
qryiso.[Unit No Iso], qryiso.[Pipe Sequence No], qryiso.[Piping Class],
qryiso.[Insulation Code], tblfilename.hyper;

 0
Utf
4/14/2010 3:50:01 AM
access.modulesdaovba 1670 articles. 0 followers.

1 Replies
828 Views

Similar Articles

[PageSpeed] 49

Hi,
you can create new calculated field in your query like
Left([Hyperlinkfield], 255) and sort by it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Masoud" <Masoud@discussions.microsoft.com> wrote in message
news:F02B1A7A-7B69-4C91-86CA-8D26202263EA@microsoft.com...
> Hello
> My query is like below, hyperlink field will not be sort in data sheet
> view.out put data is like below:
>
> thanks in advanced.
>
> Unit No Line Size Fluid Code Unit No Iso Pipe Sequence No Piping
> Class Insulation Code hyper
> 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_3.pdf
>
> 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_4.pdf
>
> 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_1.pdf
>
> 100 10 G 100 20014 G03P2 N 10-G10020014-G03P2-N_Sht_2.pdf
>
>
>
>
> my query:
>
> SELECT DISTINCTROW qryiso.[Unit No], qryiso.[Line Size], qryiso.[Fluid
> Code], qryiso.[Unit No Iso], qryiso.[Pipe Sequence No], qryiso.[Piping
> Class], qryiso.[Insulation Code], tblfilename.hyper, qryiso.[Total Sheet
> No],
> qryiso.[Issue Date], qryiso.[MaxOfIsometric Drawing Rev],
> qryiso.[MaxOfIsometric Drawing List Rev], qryiso.[Issued For],
> qryiso.Remark,
> qryiso.[DOC No], qryiso![Line Size] & "-" & qryiso![Fluid Code] & "-" &
> qryiso![Unit No Iso] & "-" & qryiso![Pipe Sequence No] & "-" &
> qryiso![Piping
> Class] & "-" & qryiso![Insulation Code] AS [line No]
> FROM qryiso LEFT JOIN tblfilename ON (qryiso.[Line Size]=tblfilename.[Line
> Size]) AND (qryiso.[Fluid Code]=tblfilename.[Fluid Code]) AND
> (qryiso.[Unit
> No Iso]=tblfilename.[Unit No Iso]) AND (qryiso.[Pipe Sequence
> No]=tblfilename.[Pipe Sequence No]) AND (qryiso.[Piping
> Class]=tblfilename.[Piping Class]) AND (qryiso.[Insulation
> Code]=tblfilename.[Insulation Code])
> WHERE (((qryiso.[DOC No]) Like "*001" Or (qryiso.[DOC No]) Like "*003"))
> ORDER BY qryiso.[Unit No], qryiso.[Line Size], qryiso.[Fluid Code],
> qryiso.[Unit No Iso], qryiso.[Pipe Sequence No], qryiso.[Piping Class],
> qryiso.[Insulation Code], tblfilename.hyper;


 0
Alex
4/14/2010 8:19:55 AM

Similar Artilces:

Changing 'From' field in received emails?
I'm running Exchange 2003 and I have a user that his name is "smith" and this is the name that appear to people outside the organization that get the mail from "smith". Smith ask me how can he change his display name to something like "Tom Smith" in the "from" field. I tried to rename the user, change the name in the properties of the user, change the Display Name in the General tab but nothing seems to work. Do I have to do it this way: http://support.microsoft.com/?kbid=250455 ? Thanks, Mike http://www.ivasoft.biz/choosefrom.shtml ...

How save SORT?
An using MS Office 2002 Excel. Have various worksheets that I always sort a certain way... let's say address list is sorted Column B, then Column A, then Column C. Problem is... everytime I go to sort, I have to re-enter this. Is there a way to save it for a specific worksheet file? Not sure, but it seems like some worksheets save it (it pops up with this sort criteria), others don't. Thanks Have you tried to record the steps as a macro (Tools|Macro|Record) Then assign the macro to a button; View|Toolbars|Forms - drag the small rectangle icon onto the sheet and assign your macr...

Motion trails:query
I would like my motion trail to be visible, so that the viewer can follow a line as it progresses. I've been learning to use motion trails and arrows, but all I get is a moving arrow, and I'd like viewers to see the trail as the animation progresses. Is there a way to do this? I'm using PP 2003. Hi, You can achieve this effect with a lines and using the appropriate wipe effect to give the impression that the lines are being drawn. -- Regards, Shyam Pillai Handout Wizard: http://skp.mvps.org/how. PowerPoint 2010 (32-bit/64-bit) versions supported. "...

Question for Stephan Lebans..or anyone who can help.
Hello, Basically I have a form with 7 tabs on it, I have been trying to change the tab font colour when a user enters information on a tab so that at a glance they can see what tabs have been updated. I realise this cannot be done so I have been looking at Stephan Lebans tab colour change code with the thought of changing the tab heading colour after the information on the tab has been updated. This would mean the tab heading would permantley change colour once updated but the main tab window would stay the same default colour. As I'm not great at coding I have come to a brick wall, woul...

separating fields #2
I've been using Quark Express to do a layout of roster/directory, and I want to tansfer all the info I have in there to an Excel spreadsheet. Quark Techs told me that I would have to cut and paste everything; which I've done. Now, I want to seperate lines 1 - 6 in to separate fields (i.e. last names in column a, first names in column b, street address in column c, city/state/zip in column d, and so on). How can I do this? ...

how we can identify the cost is negative or positive.
i open a view "Resource Cost" in project detail . how we can identify the remaining cost in loss or profit. either it is in negative or positive . why there is not showing negative cost in squrare brackets. If I understand you properly, you would like to insert some sort of a cost marker - like a contract cost, and then have Project calculate against that to determine if you made your margin or lost money....? Typically in 2007 you would do that with a Budget Cost Resource. Search the Project help screens for that. Once you've established a Budget Cost Reso...

Excel hyperlink to Word docs

Sort macro with descending
Hi... I'm trying to put in a macro that will sort the data in column G... what I have now is: Sub SortMe() Cells.Sort Key1:=Range("G1"), Header:=xlYes End Sub But, this sorts in ascending order and I need it to be descending with the highest on top? Is there a way to get it to reverse? Thanks! :) (I know I can manually sort but this will be a macro that is set to auto-run when the file is open so that's why I want it incorporated into the macro) try recording a macro while you are doing a manual sort to see what happened. Modify to suit -- Don Guillett Microsof...

How do I create a calculated field on the Total in a pivot table?
How can I create a calculation from the total of a count of a field on a pivot table? Example: I need to calculate the number of employees for each process level times a specified amount - 89 x $4.59, 3 x$4.59. Count of Name - Full Segment Process Level Total 302 302 89 305 3 302 Total 92 When I attempt to create a formula =counta('Name - Full')*4.59 I get an error. ...

can i have different baseline grids in same document?
I have a Publisher document with the main text set in 2 columns and aligned to a baseline grid. In the same document, I want to have 3 columns of text at a smaller font size to align to a baseline grid 1-2points less that the 2 columns. Is this possible in the same document? Must I have separate documents for the 2 column and 3 column pages? cheers The baseline settings are publication specific. Are you putting a check mark in the paragraph format that Aligns text to baseline? It may be helpful to do the two column text box, when finished change the baseline and then do the three col...

Use SQL to delete record from underlying query
I have a form that is a main and subform. The subform uses dlookup to find multiple values that are used as defaults for the current record. This process is complex and ends up using code to modify bound fields. This works great until I want to cancel the update. If the user wants to add a record, this form is loaded and all of the prior settings from the underlying tables are brought in. If I then click Cancel to close the form and not complete the new entry, the record is saved anyway. I understand why the record is saved by Access. I wanted to delete the just added record as a roundabout ...

sql to update inventory allocation field in IV00102
I am trying to come up with a sql script that will recalculate ATYALLOC field in IV00102. If anyone has it, email it please at jacktundra@hotmail.com ...

How do you add text to custom columns created thru the Field Chooser pls?
Hello, If I create a custom text column via Field Chooser - New, how can I enter text into it afterwards? I'm looking to add an "annotation" column and not have it sent if/when I forward or reply to the message. Outlook 2003 with Exchange 2003. Thanks, - Alan. If you directly want to fill it out in the message list you'll have to enable "allow in-cell editing" for your view. The technique would be similar to; http://www.msoutlook.info/question/150 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://ww...

Crosstab query with parameter
Hello, I have a crosstab query that has a parameter that prompts the user to enter the year of the data he/she wishes to see. The parameter is declared in the Parameter properties window as an integer and the query runs flawlessly in query mode. The moment this query is attached to a report, it throws an error indicating that "The Microsoft Jet database engine does not recognize " as a valid field name or expression." The year is extracted from the "quoteDate" field as QYY: Format(right([quoteDate],4)) Why does this crosstab work with the parameter in query m...

Field Size Decreases
Hi.. I am needing to export a table that has a field size of 80 to a fixed width text file with a field size of 55. When I do this, I lose the data an the end of the field. Is there anyway to force Access to possible create a new row of data so that I don't lose this data. I'm thinking I would do this before the export of data? These are comments that have been left in the database and we are moving to a new system which is why the field size changes and I can't change the new system. :( Any help would be great! In a query, you could use the Left function to g...

Can't delete or move from Inbox
I cannot delete or move E-mail that is in the Inbox. The following message occurs when I attempt to delete: THE MESSAGING INTERFACE HAS RETURNED AN UNKNOWN ERROR. IF THE PROBLEM PERSISTS RESTART OUTLOOK. Restart Outlook or Windows does not help, neither does re- installing Outlook. Anyone have any help? ...

How can I combine a 3-D column and stacked column chart
Imagine that your income comes from two streams. To make clearer the year-on-year differecnes you like to display a 3D chart by year by month, but use a stacked column to show the two income streams: X = month y = stacked two incomes z = year on year This doesn't seem to be one of the options. Any ideas? 3D charts cannot be combined with other chart types. They also tend to skew the data and add lots of shapes that are more distracting than elucidating. Can't you use a different chart type, in 2D? Your data isn't strictly XYZ data. A clustered column chart shows data just as ...

Need Assistance with a SQL query
I am a novice and need assistance. I have a table create table Data_price ( name varchar (34), current_price dec (12,2), current_prc_date datetime, prev_price dec (12,2), prev_prc_date datetime ) insert into Data_price values ( 'How to help', 24.99, 04/06/2010,29.99, 01/16/2010) insert into Data_price values ( 'Asian Culinary', 22.99, 12/06/2009,24.99, 10/14/2009) insert into Data_price values ( 'Asian Culinary', 16.99, 04/03/2010,22.99, 12/06/2009) insert into Data_price values ( 'I am the best', 11.97, 04/05/2010,15.99, 1...

Brain Freeze help with combobox and query
Hi, I have a personnel form with typical personnel data. On the form is an combo that has a data source of employeeID, LastName, First name. The first (0) column is bound to the combo. The query that feeds the form references the combo as criteria against the employeeID. =[ME].[cboEmployee] Or Is Null The onchange event of the combo asks for a requery of the form Private Sub cboEmployee_Change() Requery Debug.Print "it fired" End Sub The event fires but the form does not requery. Any help appreciated. Kevin kc-mass wrote: >I have a pe...

including merge fields in an oulook 2000 email
I have a number of email messages to send where most of the information is common but each has a unique piece of information in it (in addition to the address). The ideal approach appears to be a mail merge document linking a template to the merge source (contacts) Does any one know whether this is possible in outlook 2000? If so where can I find the procedure? Thanks Peter http://www.slipstick.com/addins/mail.htm#massmail --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to ...

Why can't I drag a column in Excel in XP?
In previous versions of Windows, you could highlight a column, point at the column heading (eg A/B/C etc), hold down the left hand mouse button and drag the column wherever you wanted it - effectively doing a cut and insert. Why can't you do this in XP? It will let you move the selection but it will overwrite the cells that you move it to, rather than inserting. Hi Amber can't answer the "why" part but i can give you an alternative .. use the right mouse to drag instead and then you'll get 'shift right and copy' or 'shift right and move' option...

Can Exchange 2003 empty Outlook Deleted Items Folder
How do I configure Exchange 2003 SP2 to empty old items my users' Outlook Deleted Items folder? Thanks, Mike Ober. As far as I know, you have to configure this in Outlook for each user. There aren't any settings within Exchange for this that I have seen. Hope you don't have too many users! Andy You will have to create a Mailbox Manager policy. 1. Open ESM 2. Expand the Recipients container, select the Recipient policies container, then right-click on Recipient policies and choose New, then Recipient Policy. 3. Check the Mailbox Manager settings option and click OK 4. G...

fields not displaying correctly in email merge
When creating an email merge document I have previewed the emails and they appear correctly with names inserted as expected. However when I perform a test run on selected recipients the name field appears as <<field>> rather than the actual name. Any help would be appreciated. Are you doing a test print? What version Publisher are you using? What do you see when you do a print preview? Are you merging from Outlook? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Arena Lounge" <Arena Lounge@discussions.mic...

query results
How can I display only 4 digits in a number in a query when the original data has 5. I am storing my numbers as text. JoAnn This would also apply to how to show only the first letter (initial) of a name. How to? "jkjmwilliams" wrote: > How can I display only 4 digits in a number in a query when the original data > has 5. I am storing my numbers as text. > > JoAnn "jkjmwilliams" <jkjmwilliams@discussions.microsoft.com> wrote in message news:51257658-7223-426A-8643-04C295EA377A@microsoft.com... > How can I display only 4 digits in a number in a ...

Count IF Array of sorts... What am I missing?
What I have is a spreadsheet of four columns of numbers (B11:E999). These numbers are conditionally formatted to appear in .RED if it's th highest number in the row and BLUE if it's the second highest in th row. This bit of information may be irrelevant. What I'm trying to do is add a count (ROWs above 11) of the number o times a column contains the highest or second highest number. I' thinking this is not that hard, but it's not clicking for me today. Example: Code ------------------- ___________A_______B_____C_____D_____E___ 7|Count of High | 2 | 0 | ...