I was trying to pull down values from two tables Dbo_Bridges1 and dbo_hpr using a query form named Prop_Bridges.
Dbo_Bridges1 - gives information about the bridges in a particular area
dbo_hpr - gives the property information in that particular area
What I need to do is : If I select a particular area say county and click on the query button, it should bring all the related properties and bridges in that area. Here is the SQL query I have written, but its not working. Its throwing Type mismatch error.
Note: During creation of the data, a column named COUNTYCD which is a combo box contains values in the text format, but when saved it will automatically converts in to a unique ID. For example if user inputs Name = BOB cemetry, Address: 185 N husband St, County = Payne. It will be saved as Name|Address|COUNTYCD
BOB Cemetery|185 N husband St|156.
I am using a small lookup table for this and its working fine. Here is my SQL code.Can someone help me pls writing the correct query. Thanks in advance.
Also I am not sure of selecting the "Record Source" for the Query form, since its going to extract values from 2 diff tables.
SELECT dbo_hpr.OBJECTID, dbo_hpr.PROPNAME, dbo_hpr.RESNAME, dbo_hpr.ADDRESS, dbo_hpr.CITY, dbo_hpr.VICINTIY, dbo_hpr.COUNTYCD, dbo_hpr.LOT, dbo_hpr.BLOCK, dbo_hpr.PLATNAME, dbo_hpr.SECTION, dbo_hpr.TOWNSHIP, dbo_hpr.RANGE, dbo_hpr.RESTYPE, dbo_hpr.HIST_FUNC, dbo_hpr.CURR_FUNC, dbo_hpr.AREASG_1, dbo_hpr.AREASG_2, dbo_hpr.desc_seg, dbo_hpr.DOC_SOURCE, dbo_hpr.NAME_PREP, dbo_hpr.SURVEY_PRO, dbo_hpr.Projectname, dbo_hpr.DATE_PREP, dbo_hpr.PHOTOGRAPH, dbo_hpr.Year, dbo_hpr.ARCH_BUILD, dbo_hpr.YEAR_BUILD, dbo_hpr.ORIG_SITE, dbo_hpr.DATEMOVED, dbo_hpr.FROMWHERE, dbo_hpr.ACCESSIBLE, dbo_hpr.ARCH_STYLE, dbo_hpr.OTHER_ARCH, dbo_hpr.FOUN_MAT, dbo_hpr.ROOF_TYPE, dbo_hpr.ROOF_MAT, dbo_hpr.WALL_MAT_1, dbo_hpr.WALL_MAT_2, dbo_hpr.WINDOW_TYP, dbo_hpr.WINDOW_MAT, dbo_hpr.DOOR_TYP, dbo_hpr.DOOR_MAT, dbo_hpr.EXTER_FEA, dbo_hpr.INTER_FEA, dbo_hpr.DEC_DETAIL, dbo_hpr.CONDITION, dbo_hpr.DES_RES, dbo_hpr.COMMENTS, dbo_hpr.PLACEMENT, dbo_hpr.lonr, dbo_hpr.CONTINUATION, dbo_hpr.NRData, dbo_hpr.Date_Updated, dbo_hpr.Lat, dbo_hpr.Long, dbo_hpr.UTM_Zone, dbo_hpr.Easting, dbo_hpr.Northing, dbo_hpr.P_B_C, dbo_hpr.Year_Closed, Dbo_Bridges1.F_Main_Div, Dbo_Bridges1.FHWA_No, Dbo_Bridges1.CITY, Dbo_Bridges1.ADDRESS, Dbo_Bridges1.Feat_Int, Dbo_Bridges1.Prog_Res_Date, Dbo_Bridges1.Rep_Proj_No, Dbo_Bridges1.Struct_No, Dbo_Bridges1.Date_Built, Dbo_Bridges1.Designer, Dbo_Bridges1.Builder, Dbo_Bridges1.Bridge_Plate, Dbo_Bridges1.Hist_Events, Dbo_Bridges1.Jurisdiction, Dbo_Bridges1.Struct_Use, Dbo_Bridges1.Traffic_Open, Dbo_Bridges1.Traffic_Close, Dbo_Bridges1.Main_Struct_Typ, Dbo_Bridges1.Design_Config, Dbo_Bridges1.No_Spans, Dbo_Bridges1.Struct_Len, Dbo_Bridges1.Span_Type1, Dbo_Bridges1.Span_Type2, Dbo_Bridges1.Span_Type3, Dbo_Bridges1.Span_Type4, Dbo_Bridges1.Span_Type5, Dbo_Bridges1.Span_Len1, Dbo_Bridges1.Span_Len2, Dbo_Bridges1.Span_Len3, Dbo_Bridges1.Span_Len4, Dbo_Bridges1.Span_Len5, Dbo_Bridges1.Lanes_Struct, Dbo_Bridges1.Struct_width, Dbo_Bridges1.Dec_Arch_Fea, Dbo_Bridges1.Piers, Dbo_Bridges1.Abutments, Dbo_Bridges1.Wings, Dbo_Bridges1.Seats, Dbo_Bridges1.Material, Dbo_Bridges1.Source, Dbo_Bridges1.Connections, Dbo_Bridges1.Top_Chord, Dbo_Bridges1.End_Posts, Dbo_Bridges1.Btm_Chord, Dbo_Bridges1.Posts, Dbo_Bridges1.Diagonal, Dbo_Bridges1.Counters, Dbo_Bridges1.Mis_Info, Dbo_Bridges1.Photo_No, Dbo_Bridges1.Construct_Plan, Dbo_Bridges1.Location, Dbo_Bridges1.Other_D_Config, Dbo_Bridges1.Recorder, Dbo_Bridges1.Date, Dbo_Bridges1.Title, Dbo_Bridges1.P_B_C, Dbo_Bridges1.COUNTYCD, Dbo_Bridges1.Plat_Name, Dbo_Bridges1.Section, Dbo_Bridges1.Township, Dbo_Bridges1.Range, Dbo_Bridges1.Latitude, Dbo_Bridges1.Longitude, Dbo_Bridges1.UTMZone, Dbo_Bridges1.Northings, Dbo_Bridges1.Eastings, Dbo_Bridges1.LONR, Dbo_Bridges1.NRData
FROM Dbo_Bridges1 INNER JOIN dbo_hpr ON ((Dbo_Bridges1.[CITY] = dbo_hpr.[CITY]) AND (Dbo_Bridges1.[COUNTYCD] = dbo_hpr.[COUNTYCD]))
WHERE (((dbo_hpr.ADDRESS) Like Nz(Forms!Prop_Bridges!comboaddr,"~") & "*")) OR (((Dbo_Bridges1.ADDRESS) Like Nz([Forms]![Prop_Bridges]![comboaddr],"~") & "*")) OR (((dbo_hpr.CITY) Like Nz(Forms!Prop_Bridges!combocity,"~") & "*")) OR (((Dbo_Bridges1.CITY) Like Nz([Forms]![Prop_Bridges]![combocity],"~") & "*")) OR (((dbo_hpr.COUNTYCD)=Forms!Prop_Bridges!combocounty)) OR (((Dbo_Bridges1.COUNTYCD)=[Forms]![Prop_Bridges]![Combocounty]));
Dbo_Bridges1 - gives information about the bridges in a particular area
dbo_hpr - gives the property information in that particular area
What I need to do is : If I select a particular area say county and click on the query button, it should bring all the related properties and bridges in that area. Here is the SQL query I have written, but its not working. Its throwing Type mismatch error.
Note: During creation of the data, a column named COUNTYCD which is a combo box contains values in the text format, but when saved it will automatically converts in to a unique ID. For example if user inputs Name = BOB cemetry, Address: 185 N husband St, County = Payne. It will be saved as Name|Address|COUNTYCD
BOB Cemetery|185 N husband St|156.
I am using a small lookup table for this and its working fine. Here is my SQL code.Can someone help me pls writing the correct query. Thanks in advance.
Also I am not sure of selecting the "Record Source" for the Query form, since its going to extract values from 2 diff tables.
SELECT dbo_hpr.OBJECTID, dbo_hpr.PROPNAME, dbo_hpr.RESNAME, dbo_hpr.ADDRESS, dbo_hpr.CITY, dbo_hpr.VICINTIY, dbo_hpr.COUNTYCD, dbo_hpr.LOT, dbo_hpr.BLOCK, dbo_hpr.PLATNAME, dbo_hpr.SECTION, dbo_hpr.TOWNSHIP, dbo_hpr.RANGE, dbo_hpr.RESTYPE, dbo_hpr.HIST_FUNC, dbo_hpr.CURR_FUNC, dbo_hpr.AREASG_1, dbo_hpr.AREASG_2, dbo_hpr.desc_seg, dbo_hpr.DOC_SOURCE, dbo_hpr.NAME_PREP, dbo_hpr.SURVEY_PRO, dbo_hpr.Projectname, dbo_hpr.DATE_PREP, dbo_hpr.PHOTOGRAPH, dbo_hpr.Year, dbo_hpr.ARCH_BUILD, dbo_hpr.YEAR_BUILD, dbo_hpr.ORIG_SITE, dbo_hpr.DATEMOVED, dbo_hpr.FROMWHERE, dbo_hpr.ACCESSIBLE, dbo_hpr.ARCH_STYLE, dbo_hpr.OTHER_ARCH, dbo_hpr.FOUN_MAT, dbo_hpr.ROOF_TYPE, dbo_hpr.ROOF_MAT, dbo_hpr.WALL_MAT_1, dbo_hpr.WALL_MAT_2, dbo_hpr.WINDOW_TYP, dbo_hpr.WINDOW_MAT, dbo_hpr.DOOR_TYP, dbo_hpr.DOOR_MAT, dbo_hpr.EXTER_FEA, dbo_hpr.INTER_FEA, dbo_hpr.DEC_DETAIL, dbo_hpr.CONDITION, dbo_hpr.DES_RES, dbo_hpr.COMMENTS, dbo_hpr.PLACEMENT, dbo_hpr.lonr, dbo_hpr.CONTINUATION, dbo_hpr.NRData, dbo_hpr.Date_Updated, dbo_hpr.Lat, dbo_hpr.Long, dbo_hpr.UTM_Zone, dbo_hpr.Easting, dbo_hpr.Northing, dbo_hpr.P_B_C, dbo_hpr.Year_Closed, Dbo_Bridges1.F_Main_Div, Dbo_Bridges1.FHWA_No, Dbo_Bridges1.CITY, Dbo_Bridges1.ADDRESS, Dbo_Bridges1.Feat_Int, Dbo_Bridges1.Prog_Res_Date, Dbo_Bridges1.Rep_Proj_No, Dbo_Bridges1.Struct_No, Dbo_Bridges1.Date_Built, Dbo_Bridges1.Designer, Dbo_Bridges1.Builder, Dbo_Bridges1.Bridge_Plate, Dbo_Bridges1.Hist_Events, Dbo_Bridges1.Jurisdiction, Dbo_Bridges1.Struct_Use, Dbo_Bridges1.Traffic_Open, Dbo_Bridges1.Traffic_Close, Dbo_Bridges1.Main_Struct_Typ, Dbo_Bridges1.Design_Config, Dbo_Bridges1.No_Spans, Dbo_Bridges1.Struct_Len, Dbo_Bridges1.Span_Type1, Dbo_Bridges1.Span_Type2, Dbo_Bridges1.Span_Type3, Dbo_Bridges1.Span_Type4, Dbo_Bridges1.Span_Type5, Dbo_Bridges1.Span_Len1, Dbo_Bridges1.Span_Len2, Dbo_Bridges1.Span_Len3, Dbo_Bridges1.Span_Len4, Dbo_Bridges1.Span_Len5, Dbo_Bridges1.Lanes_Struct, Dbo_Bridges1.Struct_width, Dbo_Bridges1.Dec_Arch_Fea, Dbo_Bridges1.Piers, Dbo_Bridges1.Abutments, Dbo_Bridges1.Wings, Dbo_Bridges1.Seats, Dbo_Bridges1.Material, Dbo_Bridges1.Source, Dbo_Bridges1.Connections, Dbo_Bridges1.Top_Chord, Dbo_Bridges1.End_Posts, Dbo_Bridges1.Btm_Chord, Dbo_Bridges1.Posts, Dbo_Bridges1.Diagonal, Dbo_Bridges1.Counters, Dbo_Bridges1.Mis_Info, Dbo_Bridges1.Photo_No, Dbo_Bridges1.Construct_Plan, Dbo_Bridges1.Location, Dbo_Bridges1.Other_D_Config, Dbo_Bridges1.Recorder, Dbo_Bridges1.Date, Dbo_Bridges1.Title, Dbo_Bridges1.P_B_C, Dbo_Bridges1.COUNTYCD, Dbo_Bridges1.Plat_Name, Dbo_Bridges1.Section, Dbo_Bridges1.Township, Dbo_Bridges1.Range, Dbo_Bridges1.Latitude, Dbo_Bridges1.Longitude, Dbo_Bridges1.UTMZone, Dbo_Bridges1.Northings, Dbo_Bridges1.Eastings, Dbo_Bridges1.LONR, Dbo_Bridges1.NRData
FROM Dbo_Bridges1 INNER JOIN dbo_hpr ON ((Dbo_Bridges1.[CITY] = dbo_hpr.[CITY]) AND (Dbo_Bridges1.[COUNTYCD] = dbo_hpr.[COUNTYCD]))
WHERE (((dbo_hpr.ADDRESS) Like Nz(Forms!Prop_Bridges!comboaddr,"~") & "*")) OR (((Dbo_Bridges1.ADDRESS) Like Nz([Forms]![Prop_Bridges]![comboaddr],"~") & "*")) OR (((dbo_hpr.CITY) Like Nz(Forms!Prop_Bridges!combocity,"~") & "*")) OR (((Dbo_Bridges1.CITY) Like Nz([Forms]![Prop_Bridges]![combocity],"~") & "*")) OR (((dbo_hpr.COUNTYCD)=Forms!Prop_Bridges!combocounty)) OR (((Dbo_Bridges1.COUNTYCD)=[Forms]![Prop_Bridges]![Combocounty]));
Last edited:
